<?php

namespace Home\Controller;

use Org\Util\UploadFile;
use Home\Org\Util\Sqlserver;
use Home\Org\Util\StringCV;
use Home\Org\Util\PHPTOSAP;

use Home\Org\Util\LifecycleDatabase;

use Home\Org\Util\UserRole;
use Home\Org\Util\PermissionDatabase;

use Home\Org\Util\ProductManager;
use Home\Org\Util\SalesSupervisor;
use Home\Org\Util\SapBrand;

// for new report: 20171018
use Home\Org\Util\Sqlserverforreport;

class ReportController extends HomeController {

	public function index(){
	    $this->display();
	}
	
	
	public function orderaccess(){
	
	    $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
	    $this->display('index');
	}
    
    public function brandrank(){
      
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    
    public function bptrend(){
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }

    //20170518 边缘品牌
    public function arginalbrand(){
        $this->display('arginalbrand');
    }
    //20170516 品牌排名
    public function topbrand(){
        $this->display('topbrand');
    }
    public function topbrandService(){

        if (!empty($_POST['postquery'])) {$postquery=str_replace("do",",",$_POST['postquery']);}
        if (!empty($_GET['postquery'])) {$postquery=str_replace("do",",",$_GET['postquery']); }

        import("@.ORG.Util.PHPTOSAP");
        $sqlconnection = PHPTOSAP::sqlServerConnection();
        $sqlquery="exec topbrandsale ";
        $sqlquery=$sqlquery.$postquery;
        $arr=odbc_exec($sqlconnection,$sqlquery);

        $users=array();
        $i=0;
        while($row=odbc_fetch_array($arr)){
            $users[$i]=$row;
            $i++;
        }
        import("@.ORG.Util.StringCV");
        echo $_GET['postquery']."(".json_encode(StringCV::gbk2utf8($users)).")";
    }

    public function stickstockreport(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
     public function brandtrends(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    public function classic2(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    public function brandreport(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    public function itemsearch(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    public function classic1(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    public function pmanagertrendsbyline(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    
    public function stickstockdetail(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    
    public function productkpi(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    public function salesmaps(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    public function profitcompare(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    public function profitreportx(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
        //$this->display();
    }
    public function profitreportxdata(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');

        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        //$userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $userInfo = ""; //正式服务器表结构不同步,暂时屏蔽

        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();

        // from old code
        $Cardcodes = $_REQUEST['Cardcode'];
        $CardcodeSqlFormate="'".implode("','",$Cardcodes)."'";
        $FromDate = $_REQUEST['FromDate'];
        $ToDate = $_REQUEST['ToDate'];
        $ViewOption = $_REQUEST['ViewOption'];

        $sku=$_REQUEST['sku'];
        //$pinpai=$_REQUEST['pinpai'];
        //$pinpai=iconv("utf-8//IGNORE","gbk",$_REQUEST['pinpai']);
        $pinpai=mb_convert_encoding($_REQUEST['pinpai'],'gbk','utf-8');

        if ( $ViewOption=='Total') {
            $sqlquery="select  T0.ITEMCODE,T1.ItemName,Sum(Quantity) sumqty, null CardCode,
isnull(sum([linetotal]),'0')-isnull(sum([bankcharge]),'0')-isnull(sum([calclineStockprice]),'0')-isnull(sum([calcexpressfee]),'0')-isnull(sum([addexpressfee]),'0')-isnull(sum([giftcount]),'0')-isnull(sum([giftaddexpressfee]),'0')-isnull(sum([calctax]),'0') profit,
sum(isnull(linetotal,0)) sumprice,sum(isnull(calclineStockprice,0)) sumstockprice,
sum(isnull(bankcharge,0)) sumbankfee ,
sum(isnull(calcexpressfee,0)) sumexpress ,
sum(isnull(giftcount,0)) sumgiftprice,sum(isnull(giftprice,0))sumgift ,sum(isnull(giftaddexpressfee,0)) sumgiftaddexpressfee, sum(isnull(addexpressfee,0)) sumaddstockfee,sum(isnull(calctax,0)) sumtax    from ax_reportx T0 left join OITM T1 on T0.ItemCode=t1.ItemCode where (T1.itemcode LIKE '%".$sku."%'  or '".$sku."'='') and
(T1.u_brand LIKE '%".$pinpai."%'  or '".$pinpai."'='') and T0.DocDate>='".$FromDate."' and T0.DocDate<='".$ToDate."' and T0.CardCode IN (".$CardcodeSqlFormate.")
    GROUP by t0.ITEMCODE,t1.ItemName  order by t0.itemcode";}
        if ( $ViewOption=='Everyone') {
            $sqlquery="select  T0.ITEMCODE,T1.ItemName,gt.dbo.BPcontact(T0.CardCode,'CardName') CardName,T0.CardCode CardCode,Sum(Quantity) sumqty,
    isnull(sum([linetotal]),'0')-isnull(sum([bankcharge]),'0')-isnull(sum([calclineStockprice]),'0')-isnull(sum([calcexpressfee]),'0')-isnull(sum([addexpressfee]),'0')-isnull(sum([giftcount]),'0')-isnull(sum([giftaddexpressfee]),'0')-isnull(sum([calctax]),'0') profit,
    sum(isnull(linetotal,0)) sumprice,sum(isnull(calclineStockprice,0)) sumstockprice,
    sum(isnull(bankcharge,0)) sumbankfee ,
    sum(isnull(calcexpressfee,0)) sumexpress ,
    sum(isnull(giftcount,0)) sumgiftprice,sum(isnull(giftprice,0))sumgift ,sum(isnull(giftaddexpressfee,0)) sumgiftaddexpressfee, sum(isnull(addexpressfee,0)) sumaddstockfee,sum(isnull(calctax,0)) sumtax    from ax_reportx T0 left join OITM T1 on T0.ItemCode=t1.ItemCode where (T1.itemcode LIKE '%".$sku."%'  or '".$sku."'='') and
    (T1.u_brand LIKE '%".$pinpai."%'  or '".$pinpai."'='') and T0.DocDate>='".$FromDate."' and T0.DocDate<='".$ToDate."' and T0.CardCode IN (".$CardcodeSqlFormate.")
    GROUP by t0.ITEMCODE,t1.ItemName,T0.CardCode order by t0.itemcode";}

        //$arr=odbc_do($sqlconnection,$sqlquery);
        //echo $sqlquery;

        $data = $sqlserver->query($sqlquery);
        //var_dump($data);die();
        $sqlserver->close();
        //$this->assign("sqlserver",$sqlserver);

        $this->assign("ViewOption",$ViewOption);

        $this->assign("data",$data);
        $this->display();

    }

    public function profitreportxdataExport(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');

        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        $userInfo = ""; //正式服务器表结构不同步,暂时屏蔽

        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();

        // from old code
        $Cardcodes = $_REQUEST['Cardcode'];
        $CardcodeSqlFormate="'".implode("','",$Cardcodes)."'";
        $FromDate = $_REQUEST['FromDate'];
        $ToDate = $_REQUEST['ToDate'];
        $ViewOption = $_REQUEST['ViewOption'];

        $sku=$_REQUEST['sku'];
        $pinpai=mb_convert_encoding($_REQUEST['pinpai'],'gbk','utf-8');

        if ( $ViewOption=='Total') {
            $sqlquery="select  T0.ITEMCODE,T1.ItemName,Sum(Quantity) sumqty, null CardCode,
isnull(sum([linetotal]),'0')-isnull(sum([bankcharge]),'0')-isnull(sum([calclineStockprice]),'0')-isnull(sum([calcexpressfee]),'0')-isnull(sum([addexpressfee]),'0')-isnull(sum([giftcount]),'0')-isnull(sum([giftaddexpressfee]),'0')-isnull(sum([calctax]),'0') profit,
sum(isnull(linetotal,0)) sumprice,sum(isnull(calclineStockprice,0)) sumstockprice,
sum(isnull(bankcharge,0)) sumbankfee ,
sum(isnull(calcexpressfee,0)) sumexpress ,
sum(isnull(giftcount,0)) sumgiftprice,sum(isnull(giftprice,0))sumgift ,sum(isnull(giftaddexpressfee,0)) sumgiftaddexpressfee, sum(isnull(addexpressfee,0)) sumaddstockfee,sum(isnull(calctax,0)) sumtax    from ax_reportx T0 left join OITM T1 on T0.ItemCode=t1.ItemCode where (T1.itemcode LIKE '%".$sku."%'  or '".$sku."'='') and
(T1.u_brand LIKE '%".$pinpai."%'  or '".$pinpai."'='') and T0.DocDate>='".$FromDate."' and T0.DocDate<='".$ToDate."' and T0.CardCode IN (".$CardcodeSqlFormate.")
    GROUP by t0.ITEMCODE,t1.ItemName  order by t0.itemcode";}
        if ( $ViewOption=='Everyone') {
            $sqlquery="select  T0.ITEMCODE,T1.ItemName,gt.dbo.BPcontact(T0.CardCode,'CardName') CardName,T0.CardCode CardCode,Sum(Quantity) sumqty,
    isnull(sum([linetotal]),'0')-isnull(sum([bankcharge]),'0')-isnull(sum([calclineStockprice]),'0')-isnull(sum([calcexpressfee]),'0')-isnull(sum([addexpressfee]),'0')-isnull(sum([giftcount]),'0')-isnull(sum([giftaddexpressfee]),'0')-isnull(sum([calctax]),'0') profit,
    sum(isnull(linetotal,0)) sumprice,sum(isnull(calclineStockprice,0)) sumstockprice,
    sum(isnull(bankcharge,0)) sumbankfee ,
    sum(isnull(calcexpressfee,0)) sumexpress ,
    sum(isnull(giftcount,0)) sumgiftprice,sum(isnull(giftprice,0))sumgift ,sum(isnull(giftaddexpressfee,0)) sumgiftaddexpressfee, sum(isnull(addexpressfee,0)) sumaddstockfee,sum(isnull(calctax,0)) sumtax    from ax_reportx T0 left join OITM T1 on T0.ItemCode=t1.ItemCode where (T1.itemcode LIKE '%".$sku."%'  or '".$sku."'='') and
    (T1.u_brand LIKE '%".$pinpai."%'  or '".$pinpai."'='') and T0.DocDate>='".$FromDate."' and T0.DocDate<='".$ToDate."' and T0.CardCode IN (".$CardcodeSqlFormate.")
    GROUP by t0.ITEMCODE,t1.ItemName,T0.CardCode order by t0.itemcode";}

        //echo $sqlquery;
        $data = $sqlserver->query($sqlquery);
        $sqlserver->close();

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '物料号');
        $currentSheet->setCellValue('B'.$m, '物料名');
        $currentSheet->setCellValue('C'.$m, '平台');
        $currentSheet->setCellValue('D'.$m, '数量');
        $currentSheet->setCellValue('E'.$m, '毛利');

        $currentSheet->setCellValue('F'.$m, '销售金额');
        $currentSheet->setCellValue('G'.$m, '库存价值');
        $currentSheet->setCellValue('H'.$m, '银行费用');
        $currentSheet->setCellValue('I'.$m, '快递费用');
        $currentSheet->setCellValue('J'.$m, '赠品件数');

        $currentSheet->setCellValue('K'.$m, '赠品成本');
        $currentSheet->setCellValue('L'.$m, '赠品运费');
        $currentSheet->setCellValue('M'.$m, '物流附加');
        $currentSheet->setCellValue('N'.$m, '税');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['ITEMCODE'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['ItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,round($v['sumqty'],0), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,round($v['profit'],2), \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,round($v['sumprice'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,round($v['sumstockprice'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,round($v['sumbankfee'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,round($v['sumexpress'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,round($v['sumgift'],2), \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,round($v['sumgiftprice'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,round($v['sumgiftaddexpressfee'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,round($v['sumaddstockfee'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,round($v['sumtax'],2), \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit();

    }

    public function classic3(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');

        /*import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();
        $this->assign("sqlserver",$sqlserver);
        $this->display();*/
    }
    public function bptrendx(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    public function marketingreports(){
    
        $this->assign('src', C('REPORTCONF')[__FUNCTION__]);
        $this->display('index');
    }
    
    public function sap(){
        $model = M('UserDiy');
        if(!session('sapConfig')){
            $config = $model->where(array('cname'=>'sapConfig','user'=>get_user_id()))->find();
            if(!$config){
                $config = self::getSapConfigStr();
            }
            session('sapConfig',$config['content']);
        }
       
        $this->assign('src', '/sap/index.php');
        $this->display('index');
    }
    
    
    public function scorder(){
        $config = self::getSapConfigStrBysc();
        session('sapConfig_sc',$config['content']);
        $this->assign('src', '/sap/shichang_order.php');
        $this->display('index');
    }
    
    
    
    
    function exportFreight(){

        $where = ' where 1=1 ';
        $key = I('key');
        if($key)
            $where .=" and ExpNo like '%{$key}%'";
        $start = I('start');
        $end   = I('end');
        if($start)
            $where .=" and ctime >= '{$start}'";
        
        if($end)
            $where .=" and ctime <= '{$end} 23:59:59'";
        $quersql = "SELECT * from  think_expresscostsub as a  {$where}  ";
        $model = M('Expresscostsub');
        $res = $model->query($quersql);
     
        Vendor('Excel.PHPExcel');
        
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '快递单号');
        $currentSheet->setCellValue('B'.$m, '快递公司');
        $currentSheet->setCellValue('C'.$m, '重量');
        $currentSheet->setCellValue('D'.$m, '成本');
        $currentSheet->setCellValue('E'.$m, '预估运费成本');
        $currentSheet->setCellValue('F'.$m, '可用性检查');
        $currentSheet->setCellValue('G'.$m, '省份');
        $currentSheet->setCellValue('H'.$m, '结算状态');
        $currentSheet->setCellValue('I'.$m, '导入时间');
        
        $cellIndex=2;
        foreach ($res as $k => $v){
        
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['ExpNo'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['ExpCom'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['Weight'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['Cost'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['CalcCost'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['BillStatus'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['province'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['BillStatus'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['ctime'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $cellIndex++;
            
        }
        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        die();
        
    }
    /**
     * 市场部订单导出  dem
     */
    function exportscorder(){
        $orderby = 'order by  U_DocDate desc ,U_OrderId';
        $wheres  = $_POST['q'];
        $sqlquery = '';
        foreach($wheres as $key=>$where){
            if($where){
                    $where = iconv("utf-8","gb2312", $where);
                    $sqlquery.= " and {$key} like '%{$where}%'  ";
                    $countsql.= " and {$key} like '%{$where}%'  ";
                    unset($where);
            }
        }
        
        if($_POST['udocdate']){
            $start = $_POST['udocdate']['start'];
            $end  =  $_POST['udocdate']['end'];
            $sqlquery.= "and z.U_DocDate between '{$start}' and '{$end}' ";
            $countsql.= "and z.U_DocDate between '{$start}' and '{$end}' ";
        }
        
        if($_POST['statuslist']){
            $statusStr= $_POST['statuslist'];
            $sqlquery.= " AND U_OStatus in ({$statusStr}) ";
            $countsql.= " AND U_OStatus in ({$statusStr}) ";
        }
        
        $sqlquery="SELECT * FROM ( SELECT row_number () OVER (  {$orderby} ) id ,* FROM ( SELECT U_CardCode, t.U_Total AS total, t.* FROM [@ti_z0081] AS t LEFT JOIN [@ti_z0080] AS z ON t.DocEntry = z.DocEntry where 1=1 {$sqlquery} ) t1 ) t2 ";
        import("@.ORG.Util.Sqlserver");
        $db = new Sqlserver();
        $data = $db->query($sqlquery);
        $db->close();
        Vendor('Excel.PHPExcel');
        
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '订单编号');
        $currentSheet->setCellValue('B'.$m, '订单当前状态');
        $currentSheet->setCellValue('C'.$m, '订单优先级');
        $currentSheet->setCellValue('D'.$m, '物料代码');
        $currentSheet->setCellValue('E'.$m, '颜色');
        $currentSheet->setCellValue('F'.$m, '物料描述');
        $currentSheet->setCellValue('G'.$m, '数量');
        $currentSheet->setCellValue('H'.$m, '行类型');
        $currentSheet->setCellValue('I'.$m, '结算价');
        $currentSheet->setCellValue('J'.$m, '发票抬头');
        $currentSheet->setCellValue('K'.$m, '备注');
        $currentSheet->setCellValue('L'.$m, '订单确定日期');
        $currentSheet->setCellValue('M'.$m, '已发货时间');
        $currentSheet->setCellValue('N'.$m, '是否代发');
        $currentSheet->setCellValue('O'.$m, '发票类型');
        $currentSheet->setCellValue('P'.$m, '是否拣配');
        $currentSheet->setCellValue('Q'.$m, '快递公司');
        $currentSheet->setCellValue('R'.$m, '快递号');
        $currentSheet->setCellValue('S'.$m, '发货序号');
        $currentSheet->setCellValue('T'.$m, '客户代码');
        $currentSheet->setCellValue('U'.$m, '客户描述');
        $currentSheet->setCellValue('V'.$m, '产品经理');
        $currentSheet->setCellValue('W'.$m, '产品性质');
        $currentSheet->setCellValue('X'.$m, '售前单号');
        $currentSheet->setCellValue('Y'.$m, '售后单号');
        $currentSheet->setCellValue('Z'.$m, '客户物料号');
        $currentSheet->setCellValue('AA'.$m, '客户物料名称');
        $currentSheet->setCellValue('AB'.$m, '总计');
        $currentSheet->setCellValue('AC'.$m, '单据日期');
        $currentSheet->setCellValue('AD'.$m, '导入单号');
        $currentSheet->setCellValue('AE'.$m, '导入单行');
        $currentSheet->setCellValue('AF'.$m, '商品型号');
        $currentSheet->setCellValue('AG'.$m, '省');
        $currentSheet->setCellValue('AH'.$m, '市');
        $currentSheet->setCellValue('AI'.$m, '县');
        $currentSheet->setCellValue('AJ'.$m, '订单取消原因'); 
        $currentSheet->setCellValue('AK'.$m, '订单取消备注');
        $currentSheet->setCellValue('AL'.$m, '开票备注');
        $currentSheet->setCellValue('AM'.$m, '快递费');
        $currentSheet->setCellValue('AN'.$m, '核重');
        $currentSheet->setCellValue('AO'.$m, '发票号');
        $currentSheet->setCellValue('AP'.$m, '开票日期');
        $currentSheet->setCellValue('AQ'.$m, '财务是否开票');
        $currentSheet->setCellValue('AR'.$m, '发票已寄出');
        $currentSheet->setCellValue('AS'.$m, '仓库代码');
        $currentSheet->setCellValue('AT'.$m, '发货仓库');
        $currentSheet->setCellValue('AU'.$m, '客户名称');
        $currentSheet->setCellValue('AV'.$m, '行状态');
       
        
        $cellIndex=2;
        foreach ($data as $k => $v){
            
                $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_OrderId'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('B'.$cellIndex,$this->_getOrderStatus($v['U_OStatus']), \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['U_YXJ'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_Color'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['U_ItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['U_Qty'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['U_Type'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['U_NoDPrc'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['U_InHead'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['U_Memo'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['U_IdenDate'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('M'.$cellIndex,'', \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['U_Replace'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_InType'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['U_IsPick'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['U_ExpCom'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['U_ExpNo'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('S'.$cellIndex,$v['U_SerNum'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('T'.$cellIndex,$v['U_CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('U'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('V'.$cellIndex,$v['U_PManager'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('W'.$cellIndex,'', \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('X'.$cellIndex,'', \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('Y'.$cellIndex,'', \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('Z'.$cellIndex,$v['U_CitemCd'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AA'.$cellIndex,$v['U_CitemNm'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AB'.$cellIndex,$v['total'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AC'.$cellIndex,$v['U_DocDate'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AD'.$cellIndex,$v['DocEntry'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AE'.$cellIndex,$v['LineId'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AF'.$cellIndex,$v['U_Model'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AG'.$cellIndex,$v['U_State'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AH'.$cellIndex,$v['U_City'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AI'.$cellIndex,$v['U_County'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AJ'.$cellIndex,$v['U_IsClose'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AK'.$cellIndex,$v['U_ClsMemo'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AL'.$cellIndex,$v['U_InvMemo'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AM'.$cellIndex,$v['U_ExpCost'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AN'.$cellIndex,$v['U_Weight'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AO'.$cellIndex,$v['U_InNo'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AP'.$cellIndex,$v['U_InDate'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AQ'.$cellIndex,$v['U_IsKP'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AR'.$cellIndex,$v['U_IsInvD'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AS'.$cellIndex,$v['U_WhsCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AT'.$cellIndex,'', \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AU'.$cellIndex,'', \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('AV'.$cellIndex,$v['U_LineSts'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $cellIndex ++;
        }
        // 输出
        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        
        die();
    }
    
    /**
     * 赠品补发
     */
    public  function reissue(){
        
        $this->assign('src', '/reissue/index.php');
        $this->display('index');
    }
    
    function _getOrderStatus($status){
        
        $statuslist = array(1=>'未处理',2=>'订单确认',3=>'待处理',4=>'异常订单',5=>'未发货取消',6=>'异常已导出',7=>'已发订单确认短信',8=>'可拣配',9=>'已发货',10=>'已发签收短信',11=>'确认收货',12=>'已发货待取消',13=>'丢件/损坏',14=>'已退回');
        return $statuslist[$status];
    }
    
    
    public function newexpressQuery(){
       $this->assign('src', '/express/new.php');
        $this->display('index');
    }
    
    
    
    /**
     * 快递查询
     */
public  function expressQuery(){
    ini_set("max_execution_time", "1800");
        $html = "";
   
        if($_POST){
            $is_sign = 0;
            $url = 'http://192.168.1.177:81/exp/?service=Express.query';
            $post=$_POST['xsource'];
            $inputdata= explode("\n", $post);
            $total = count($inputdata)-1;
            //echo count($inputdata);
            $html.=  '<table width="1511" border="1">';
            $html.=  '<tr>';
            $html.=  '<th width="100" scope="col">快递公司</th>';
            $html.=  '<th width="114" scope="col">快递单号</th>';
            $html.=  '<th width="80" scope="col">状态</th>';
            $html.=  '<th width="150" scope="col">签收时间</th>';
            $html.=  '<th width="200" scope="col">签收信息</th>';
            $html.=  '<th width="150" scope="col">揽件时间</th>';
            $html.=  '<th width="200" scope="col">揽件信息</th>';
            
            $html.=  '</tr>';
            for ($i=0;$i <=count($inputdata)-2; $i++){
                $inputdataline=explode("\t", $inputdata[$i]);
                
                $expno = substr($inputdataline[1],0,strlen($inputdataline[1])-1);//快递单号
                $type = $inputdataline[0];//快递公司;
               // echo $expno;
                //echo $type;
                $expcode=rtrim($expno);
         
                $res =  json_decode($this->httpPost($url, array('expNo'=>$expcode,'isSimlple'=>1)),1);
                $status =  $res['data']['expStatus']==2?"签收":'未签收';
                if($res['data']['expStatus']==2){
                    $is_sign++;
                }
               // print_r($res);
                 $html.= '<tr>';
                 $html.= '<td>'.$type.'</td>';
                 $html.= '<td>'.$res['data']['expNo'].'</td>';
                 $html.= '<td>'.$status.'</td>';
                 $html.= '<td>'.$res['data']['end'].'</td>';
                 $html.= '<td>'.$res['data']['endInfo'].'</td>';
                 $html.= '<td>'.$res['data']['start'].'</td>';
                 $html.= '<td>'.$res['data']['startInfo'].'</td>';
                 $html.= '<td>'."".'</td>';
       
            }
           
            
             $html.= '</table>';
             $html.= '总查询数量:'.$total . '已签收:'.$is_sign;
             $this->assign('html',$html);
             $this->display('express');
            
        }else{
      
      
        $this->assign('src', '/express/index.php');
        $this->display('index');
        }
    
}
public function jhuploadexcel(){
    if (!empty($_FILES)) {
    import("@.ORG.UploadFile");
    $config=array(
        'allowExts'=>array('xlsx','xls'),
        'savePath'=>'./exceltojh/',
        'saveRule'=>'time',
    );
    $upload = new UploadFile($config);
    if (!$upload->upload($config['savePath'])) {
        $this->error($upload->getErrorMsg());
    } else {
        $info = $upload->getUploadFileInfo();
        $name = $info[0]['savename'];
        Vendor('Excel.PHPExcel');
        $filename= $config['savePath'].$name;
        //导入thinkphp第三方类库
        $objPHPExcel = \PHPExcel_IOFactory::load($filename);

        $sheetData   = $objPHPExcel -> getActiveSheet() -> toArray(null, true, true, true);
    
        $currentSheet = $objPHPExcel->getSheet(0);
        $count       = count($sheetData);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '订单编号');
        $currentSheet->setCellValue('B'.$m, '商品编号');
        $currentSheet->setCellValue('C'.$m, '商品名称');
        $currentSheet->setCellValue('D'.$m, '收货人姓名');
        $currentSheet->setCellValue('E'.$m, '手机号');
        $currentSheet->setCellValue('F'.$m, '订单时间');
        $currentSheet->setCellValue('G'.$m, '支付时间');
        $currentSheet->setCellValue('H'.$m, '订单状态');
        $currentSheet->setCellValue('I'.$m, '积分单价');
        $currentSheet->setCellValue('J'.$m, '购买数量');
        $currentSheet->setCellValue('K'.$m, '积分总额');
        $currentSheet->setCellValue('L'.$m, '结算单价');
        $currentSheet->setCellValue('M'.$m, '结算总额');
        $currentSheet->setCellValue('N'.$m, '结算状态');
        $currentSheet->setCellValue('O'.$m, '结算日期');
        $currentSheet->setCellValue('P'.$m, '配送地址');
        $currentSheet->setCellValue('Q'.$m, '发货时间');
        $currentSheet->setCellValue('R'.$m, '完成时间');
        $currentSheet->setCellValue('S'.$m, '会员号');
        $currentSheet->setCellValue('T'.$m, '客户留言');
        $currentSheet->setCellValue('U'.$m, 'VIP');
        $currentSheet->setCellValue('V'.$m, '备注');
        $currentSheet->setCellValue('W'.$m, '颜色');
        $currentSheet->setCellValue('X'.$m, '平台时间');
        for ($i = 2; $i <= $count; $i++) {
            $currentSheet->setCellValue('A'.$i,'JH'.$sheetData[$i]['A']);
            $currentSheet->setCellValue('W'.$i,'无');
            $currentSheet->setCellValue('X'.$i,$sheetData[$i]['G']);
            
        }
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save($filename);
        $downloadname =ltrim($config['savePath'],'.').$name;
        header("Location:"."http://".$_SERVER['HTTP_HOST']."/$downloadname");
        
    }
    }
    exit;
}   
public function yduploadexcel(){
    if (!empty($_FILES)) {
        import("@.ORG.UploadFile");
        $config=array(
            'allowExts'=>array('xlsx','xls'),
            'savePath'=>'./exceltojh/',
            'saveRule'=>'time',
        );
        $upload = new UploadFile($config);
        if (!$upload->upload($config['savePath'])) {
            $this->error($upload->getErrorMsg());
        } else {
            $info = $upload->getUploadFileInfo();
            $name = $info[0]['savename'];
            Vendor('Excel.PHPExcel');
            $filename= $config['savePath'].$name;
            //导入thinkphp第三方类库
            $objPHPExcel = \PHPExcel_IOFactory::load($filename);

            $sheetData   = $objPHPExcel -> getActiveSheet() -> toArray(null, true, true, true);

            $currentSheet = $objPHPExcel->getSheet(0);
            $count       = count($sheetData);
            $m = 1;
            $currentSheet->setCellValue('A'.$m, '订单编号');
            $currentSheet->setCellValue('B'.$m, '下单时间');
            $currentSheet->setCellValue('C'.$m, '付款时间');
            $currentSheet->setCellValue('D'.$m, '收货人姓名');
            $currentSheet->setCellValue('E'.$m, '客户手机号');
            $currentSheet->setCellValue('F'.$m, '收货电话');
            $currentSheet->setCellValue('G'.$m, '地址');
            $currentSheet->setCellValue('H'.$m, '发票金额');
            $currentSheet->setCellValue('I'.$m, '发票抬头');
            $currentSheet->setCellValue('J'.$m, '商品信息');
            $currentSheet->setCellValue('K'.$m, '商品编码');
            $currentSheet->setCellValue('L'.$m, '配送方式');
            $currentSheet->setCellValue('M'.$m, '配送时段');
            $currentSheet->setCellValue('N'.$m, '支付方式');
            $currentSheet->setCellValue('O'.$m, '订单商品总额');
            $currentSheet->setCellValue('P'.$m, '运费');
            $currentSheet->setCellValue('Q'.$m, '订单总金额');
            $currentSheet->setCellValue('R'.$m, '应付现金');
            $currentSheet->setCellValue('S'.$m, '积分抵扣');
            $currentSheet->setCellValue('T'.$m, '数量');
            $currentSheet->setCellValue('U'.$m, '颜色');
            $currentSheet->setCellValue('V'.$m, '平台时间');
            $currentSheet->setCellValue('W'.$m, '');
            $currentSheet->setCellValue('X'.$m, '');
 
            for ($i = 2; $i <= $count; $i++) {
                $currentSheet->setCellValue('A'.$i,$sheetData[$i]['A']);
                $currentSheet->setCellValue('B'.$i,$sheetData[$i]['C']);
                $currentSheet->setCellValue('C'.$i,$sheetData[$i]['D']);
                $currentSheet->setCellValue('D'.$i,$sheetData[$i]['F']);
                $currentSheet->setCellValue('E'.$i,$sheetData[$i]['E']);
                $currentSheet->setCellValue('F'.$i,$sheetData[$i]['H']);
                $currentSheet->setCellValue('G'.$i,$sheetData[$i]['I']);
                $currentSheet->setCellValue('H'.$i,$sheetData[$i]['J']);
                $currentSheet->setCellValue('I'.$i,$sheetData[$i]['K']);
                $currentSheet->setCellValue('J'.$i,$sheetData[$i]['L']);
                $currentSheet->setCellValue('K'.$i,$sheetData[$i]['N']);
                $currentSheet->setCellValue('L'.$i,$sheetData[$i]['O']);
                $currentSheet->setCellValue('M'.$i,$sheetData[$i]['P']);
                $currentSheet->setCellValue('N'.$i,$sheetData[$i]['Q']);
                $currentSheet->setCellValue('O'.$i,$sheetData[$i]['R']);
                $currentSheet->setCellValue('P'.$i,$sheetData[$i]['S']);
                $currentSheet->setCellValue('Q'.$i,$sheetData[$i]['T']);
                $currentSheet->setCellValue('R'.$i,$sheetData[$i]['U']);
                $currentSheet->setCellValue('S'.$i,$sheetData[$i]['V']);
                $currentSheet->setCellValue('T'.$i,'1');
                $currentSheet->setCellValue('U'.$i,'无');
                $currentSheet->setCellValue('V'.$i,$sheetData[$i]['C']);
                $currentSheet->setCellValue('W'.$i,'');
                $currentSheet->setCellValue('X'.$i,'');
            }
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save($filename);
            $downloadname =ltrim($config['savePath'],'.').$name;
            header("Location:"."http://".$_SERVER['HTTP_HOST']."/$downloadname");

        }
     }
    exit;
}

public function yd2uploadexcel(){
    if (!empty($_FILES)) {
        import("@.ORG.UploadFile");
        $config=array(
            'allowExts'=>array('xlsx','xls'),
            'savePath'=>'./exceltojh/',
            'saveRule'=>'time',
        );
        $upload = new UploadFile($config);
        if (!$upload->upload($config['savePath'])) {
            $this->error($upload->getErrorMsg());
        } else {
            $info = $upload->getUploadFileInfo();
            $name = $info[0]['savename'];
            Vendor('Excel.PHPExcel');
            $filename= $config['savePath'].$name;
            //导入thinkphp第三方类库
            $objPHPExcel = \PHPExcel_IOFactory::load($filename);

            $sheetData   = $objPHPExcel -> getActiveSheet() -> toArray(null, true, true, true);

            $currentSheet = $objPHPExcel->getSheet(0);
            $count       = count($sheetData);
            $m = 1;
            $currentSheet->setCellValue('A'.$m, '订单编号');
            $currentSheet->setCellValue('B'.$m, '下单时间');
            $currentSheet->setCellValue('C'.$m, '付款时间');
            $currentSheet->setCellValue('D'.$m, '收货人姓名');
            $currentSheet->setCellValue('E'.$m, '客户手机号');
            $currentSheet->setCellValue('F'.$m, '收货电话');
            $currentSheet->setCellValue('G'.$m, '地址');
            $currentSheet->setCellValue('H'.$m, '发票金额');
            $currentSheet->setCellValue('I'.$m, '发票抬头');
            $currentSheet->setCellValue('J'.$m, '商品信息');
            $currentSheet->setCellValue('K'.$m, '商品编码');
            $currentSheet->setCellValue('L'.$m, '配送方式');
            $currentSheet->setCellValue('M'.$m, '配送时段');
            $currentSheet->setCellValue('N'.$m, '支付方式');
            $currentSheet->setCellValue('O'.$m, '订单商品总额');
            $currentSheet->setCellValue('P'.$m, '运费');
            $currentSheet->setCellValue('Q'.$m, '订单总金额');
            $currentSheet->setCellValue('R'.$m, '应付现金');
            $currentSheet->setCellValue('S'.$m, '积分抵扣');
            $currentSheet->setCellValue('T'.$m, '数量');
            $currentSheet->setCellValue('U'.$m, '颜色');
            $currentSheet->setCellValue('V'.$m, '平台时间');
            $currentSheet->setCellValue('W'.$m, '');
            $currentSheet->setCellValue('X'.$m, '');

            for ($i = 2; $i <= $count; $i++) {
                $currentSheet->setCellValue('A'.$i,$sheetData[$i]['A']);
                $currentSheet->setCellValue('B'.$i,$sheetData[$i]['C']);
                $currentSheet->setCellValue('C'.$i,$sheetData[$i]['D']);
                $currentSheet->setCellValue('D'.$i,$sheetData[$i]['F']);
                $currentSheet->setCellValue('E'.$i,$sheetData[$i]['E']);
                $currentSheet->setCellValue('F'.$i,$sheetData[$i]['H']);
                $currentSheet->setCellValue('G'.$i,$sheetData[$i]['I']);
                $currentSheet->setCellValue('H'.$i,$sheetData[$i]['J']);
                $currentSheet->setCellValue('I'.$i,$sheetData[$i]['K']);
                $currentSheet->setCellValue('J'.$i,$sheetData[$i]['L']);
                $currentSheet->setCellValue('K'.$i,$sheetData[$i]['N']);
                $currentSheet->setCellValue('L'.$i,$sheetData[$i]['O']);
                $currentSheet->setCellValue('M'.$i,$sheetData[$i]['P']);
                $currentSheet->setCellValue('N'.$i,$sheetData[$i]['Q']);
                $currentSheet->setCellValue('O'.$i,$sheetData[$i]['R']);
                $currentSheet->setCellValue('P'.$i,$sheetData[$i]['S']);
                $currentSheet->setCellValue('Q'.$i,$sheetData[$i]['T']);
                $currentSheet->setCellValue('R'.$i,$sheetData[$i]['U']);
                $currentSheet->setCellValue('S'.$i,$sheetData[$i]['V']);
                $currentSheet->setCellValue('T'.$i,'1');
                $currentSheet->setCellValue('U'.$i,'无');
                $currentSheet->setCellValue('V'.$i,$sheetData[$i]['C']);
                $currentSheet->setCellValue('W'.$i,'');
                $currentSheet->setCellValue('X'.$i,'');
            }
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save($filename);
            $downloadname =ltrim($config['savePath'],'.').$name;
            header("Location:"."http://".$_SERVER['HTTP_HOST']."/$downloadname");

        }
    }
    exit;
}
public function gfuploadexcel(){
    $time =time();
    $html = "";
    $errorlist = array();
    $echoStr = '';
    $cellIndex = 2;
    ini_set("max_execution_time", "1800");
    import("@.ORG.Util.Sqlserver");
     $db = new Sqlserver();
    if (!empty($_FILES)) {
        import("@.ORG.UploadFile");
        $config=array(
            'allowExts'=>array('xlsx','xls'),
            'savePath'=>'./exceltojh/',
            'saveRule'=>'time',
        );
        $upload = new UploadFile($config);
        if (!$upload->upload($config['savePath'])) {
            $this->error($upload->getErrorMsg());
        } else {
            $is_sign = 0;
            $info = $upload->getUploadFileInfo();
            $name = $info[0]['savename'];
            Vendor('Excel.PHPExcel');
            $filename= $config['savePath'].$name;
            //导入thinkphp第三方类库
            $objPHPExcel = \PHPExcel_IOFactory::load($filename);
            $sheetData   = $objPHPExcel -> getActiveSheet() -> toArray(null, true, true, true);
            $currentSheet = $objPHPExcel->getSheet(0);
            $allColumn = $currentSheet->getHighestColumn();//取到最大列数
            $allRow = $currentSheet->getHighestRow();//取到最大行数
           // echo $allRow;
           
            unset($sheetData[1],$sheetData[2],$sheetData[3],$sheetData[4]); //删除excel表头 列名等行数据
            foreach ($sheetData as $key => $v) {
                if(!$v) continue;
                $tempArr =array();
                //$tempArr['Xh'] = trim($v['A']);
                $tempArr['U_OrderId'] = trim($v['B']);
                $tempArr['U_CitemCd'] = trim($v['C']);
                $tempArr['U_CitemNm'] = trim($v['D']);
                $tempArr['U_Price'] = trim($v['E']);
                $tempArr['U_Qty'] = trim($v['H']);
                $tempArr['U_FQQTY'] = trim($v['I']);
                $tempArr['U_GoodsFee'] = trim($v['J']);
                $tempArr['U_Color'] = trim($v['O']);
                $shname=$tempArr['U_Cstname'] = trim($v['U']);
                $tempArr['U_Phone'] = trim($v['V']);
                $tempArr['U_OTPhone'] = trim($v['W']);
                $tempArr['U_Address'] = trim($v['X']);
                $tempArr['U_zipcode'] = trim($v['Y']);
                $tempArr['U_Memo'] = trim($v['AA']);
                
                $sql2="select U_Is3C from [@ti_z0041] where U_CItemCode='{$v['C']}'";
                $is3C=$db->query($sql2)[0]['U_Is3C'];
                
                if(strstr($v['D'],"新品首发")){
                    $tempArr['U_DisCt'] = trim(0.03*$v['E']);
                }
                else if(strstr($v['D'],"秒杀")){
                     
                    $tempArr['U_DisCt'] = trim(0.03*$v['E']);
                
                }
                else if(strstr($v['D'],"荷兰拍")){
                     
                    $tempArr['U_DisCt'] = trim(0.03*$v['E']);
                
                }
                else if(strstr($v['D'],"天天剁手")){
                     
                    $tempArr['U_DisCt'] = trim(0.03*$v['E']);
                
                }
                else if(strstr($v['D'],"团购")){
                    if($v['I']<0002){
                         $tempArr['U_DisCt'] = trim(0.03*$v['E']);
                    }
                    else{
                         $tempArr['U_DisCt'] = trim(0.05*$v['E']);
                    }
                
                }
                else if(strstr($v['D'],"新客户")){
                    if($v['I']<0002){
                         $tempArr['U_DisCt'] = trim(0.03*$v['E']);
                    }
                    else{
                        $tempArr['U_DisCt'] = trim(0.05*$v['E']);
                    }
                
                }
                else if(strstr($v['D'],"高端客户")){
                    if($v['I']<0002){
                         $tempArr['U_DisCt'] = trim(0.03*$v['E']);
                    }
                    else{
                         $tempArr['U_DisCt'] = trim(0.05*$v['E']);
                    }
                
                }
                else if(strstr($v['D'],"全积分")){
                    if($v['I']<0002){
                         $tempArr['U_DisCt'] = trim(0.03*$v['E']);
                    }
                    else{
                         $tempArr['U_DisCt'] = trim(0.05*$v['E']);
                    }
                
                }
                else if(strstr($v['D'],"反季特卖")){
                    if($v['I']<0002){
                         $tempArr['U_DisCt'] = trim(0.03*$v['E']);
                    }
                    else{
                         $tempArr['U_DisCt'] = trim(0.05*$v['E']);
                    }
                
                }else{
                    if($is3C=="3C"){
                
                        switch ($v['I'])
                        {
                            case '0001':
                                 $tempArr['U_DisCt'] = trim(0.03*$v['E']);
                                break;
                            case '0003':
                                 $tempArr['U_DisCt'] = trim(0.05*$v['E']);
                                break;
                            case '0006':
                                 $tempArr['U_DisCt'] = trim(0.05*$v['E']);
                                break;
                            case '0009':
                                 $tempArr['U_DisCt'] = trim(0.07*$v['E']);
                                break;
                            case '0012':
                                 $tempArr['U_DisCt'] = trim(0.07*$v['E']);
                                break;
                            case '0018':
                                 $tempArr['U_DisCt'] = trim(0.09*$v['E']);
                                break;
                            case '0024':
                                $tempArr['U_DisCt'] = trim(0.1*$v['E']);
                                break;
                        }
                
                    }
                
                    else {
                        switch ($v['I'])
                        {
                            case '0001':
                                 $tempArr['U_DisCt'] = trim(0.03*$v['E']);
                                break;
                            case '0003':
                                 $tempArr['U_DisCt'] = trim(0.08*$v['E']);
                                break;
                            case '0006':
                                $tempArr['U_DisCt'] = trim(0.08*$v['E']);
                                break;
                            case '0009':
                                $tempArr['U_DisCt'] = trim(0.08*$v['E']);
                                break;
                            case '0012':
                
                                $tempArr['U_DisCt'] = trim(0.08*$v['E']);
                                break;
                            case '0018':
                                $tempArr['U_DisCt'] = trim(0.09*$v['E']);
                                break;
                            case '0024':
                                $tempArr['U_DisCt'] = trim(0.1*$v['E']);
                                break;
                        }
                
                     }
                }
                    $tempArr['U_DocDate'] = date("Y-m-d H:i:s",strtotime(substr($v['B'], 0,8)));
                    $tempArr['U_Total'] = trim($v['E']);
                    $tempArr['U_InptCode'] = "T013";
                    $tempArr['U_CardCode'] = "C20020";
                    $tempArr['U_Bat'] = $time;
                    $str = "'".implode("','", $tempArr)."'";
             
             /*   else  if (strstr($v['X'],"请款")){
                    array_push($errorlist, $tempArr);
                }
                else  if ($v['J']==200){
                    array_push($errorlist, $tempArr);
                }
                else   if(eregi("[^\x80-\xff]","$shname")){
                    array_push($errorlist, $tempArr);
                } */
       
                    $sql =" insert into [@Auto_Import]  (" .implode(',', array_keys($tempArr) ) .") values({$str})" ;
                    $sql = iconv('utf-8', 'gbk', $sql) ;
                    $saveres = $db->saveBysql($sql);
                   
             
                     
        }
            
    }
                $sqll="[dbo].[Z0_AutoImport] '{$time}'";
                $res = $db->query($sqll);
   }
   
   
                //数据导入成功后 查询存储过程生成错误数据记录并导出excel   
               $sql3 = "select KeyEntry,ErrorMessage from [@Error_Message]";
               $iserror=$db->query($sql3);
              // error_log(var_export($iserror,1));
               foreach ($iserror as $vo){
                   $sql4 = "select U_OrderId,U_CitemCd,U_CitemNm,U_Price,U_Qty,U_FQQTY,U_GoodsFee,U_Color,U_Cstname,U_Phone,U_OTPhone,U_Address,U_zipcode,U_Memo,U_DisCt,U_DocDate,U_Total from [@Auto_Import] where KeyCode='{$vo['KeyEntry']}' and U_Bat='{$time}'";
                   $ress = $db->query($sql4);
              
                   if($ress){
                     foreach ($ress as $key=>$tmpvo){
                         $ress[$key]['U_ErrorMessage'] = $vo['ErrorMessage'];
                     }
                       array_push($errorlist, $ress);
                   }
               }
               //返回错误原来的数据表格
               $objPHPExcel = new \PHPExcel();
               $currentSheet = $objPHPExcel->getSheet(0);
               $m = 1;
               $currentSheet->setCellValue('A'.$m, '订单编号');
               $currentSheet->setCellValue('B'.$m, '商品编码');
               $currentSheet->setCellValue('C'.$m, '商品名称');
               $currentSheet->setCellValue('D'.$m, '商品价格');
               $currentSheet->setCellValue('E'.$m, '数量');
               $currentSheet->setCellValue('F'.$m, '购买期数');
               $currentSheet->setCellValue('G'.$m, '现金支付金额');
               $currentSheet->setCellValue('H'.$m, '颜色');
               $currentSheet->setCellValue('I'.$m, '收货人');
               $currentSheet->setCellValue('J'.$m, '收货人电话1');
               $currentSheet->setCellValue('K'.$m, '收货人电话2');
               $currentSheet->setCellValue('L'.$m, '收货地址');
               $currentSheet->setCellValue('M'.$m, '收货人邮编');
               $currentSheet->setCellValue('N'.$m, '用户留言');
               $currentSheet->setCellValue('O'.$m, '手续费');
               $currentSheet->setCellValue('P'.$m, '订单日期');
               $currentSheet->setCellValue('Q'.$m, '总计');
               $currentSheet->setCellValue('R'.$m, '错误信息');
              
               foreach ($errorlist as $k => $vo){
                   foreach ($vo as $v){
                   $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_OrderId'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CitemCd'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['U_CitemNm'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['U_Price'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_Qty'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['U_FQQTY'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['U_GoodsFee'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['U_Color'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['U_Cstname'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['U_Phone'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['U_OTPhone'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['U_Address'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['U_zipcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['U_Memo'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_DisCt'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['U_DocDate'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['U_Total'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['U_ErrorMessage'], \PHPExcel_Cell_DataType::TYPE_STRING);
                   $cellIndex ++;
                   }
               }
               $zj=$allRow-4;
               $cg=$zj-count($errorlist);
               if($cg<0){
                   $cg=0;
               }
               $currentSheet->setCellValueExplicit('A'.$cellIndex,'统计', \PHPExcel_Cell_DataType::TYPE_STRING);
               $currentSheet->setCellValueExplicit('B'.$cellIndex,'总计:'.$zj.'行,'.'成功导入'.$cg.'行', \PHPExcel_Cell_DataType::TYPE_STRING);
               // 输出
               $name = uniqid().'.xls';
               header('Content-Type: application/vnd.ms-excel');
               header('Content-Disposition: attachment;filename="' . $name );
               header('Cache-Control: max-age=0');
               $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
               $objWriter->save('php://output');
             //  $result = count($errorlist);
              // $html.= '总插入数量:'.$allRow;
  }
  public function yfupdate(){
      ini_set("max_execution_time", "1800");
      import("@.ORG.Util.Sqlserver");
      $db = new Sqlserver();
      $post=$_POST[xsource];
      $cellIndex = 2;
      Vendor('Excel.PHPExcel');
      $inputdata= explode("\n", $post);
      $html = "";
      $errorlist = array();
      $is_sign = 0;
      $is_sign2 = 0;
      $time =time();
     // echo '开始时间:'.$time;
      FOR ($i = 0; $i <= count($inputdata)-2; $i++)
      {
          $inputdataline=explode("\t", $inputdata[$i]);
          $sql="update [@TI_Z0091] set U_ExpCost='".trim($inputdataline[1])."' where U_ExpNo='".$inputdataline[0]."'";
          $sql2="select * from [@TI_Z0091]  where U_ExpNo='".$inputdataline[0]."'";
          $rst2=$db ->query($sql2);
        
          
          $rst=$db ->query($sql);
          $sql="update [@TI_Z0081] set U_ExpCost='".trim($inputdataline[1])."' where U_ExpNo='".$inputdataline[0]."'";
          $sql2="select * from [@TI_Z0081]  where U_ExpNo='".$inputdataline[0]."'";
          $rst2=$db ->query($sql2);
          $rst=$db ->query($sql);
          if(!$rst2){
              $is_sign2++;
              array_push($errorlist, array($inputdataline[0],$inputdataline[1]));
          }
          if($rst2){
              $is_sign++;
          }
         
      }
      
      
      $time2 =time();
      //echo '运费成功更新:'.$is_sign.'条';
     // echo '结束时间:'.$time2;
      $objPHPExcel = new \PHPExcel();
      $currentSheet = $objPHPExcel->getSheet(0);
      $m = 1;
      $currentSheet->setCellValue('A'.$m, '快递号');
      $currentSheet->setCellValue('B'.$m, '运费');
      $currentSheet->setCellValue('C'.$m, '失败原因');
      foreach ($errorlist as $k => $vo){
          
              $currentSheet->setCellValueExplicit('A'.$cellIndex,$vo[0], \PHPExcel_Cell_DataType::TYPE_STRING);
              $currentSheet->setCellValueExplicit('B'.$cellIndex,$vo[1], \PHPExcel_Cell_DataType::TYPE_STRING);
              $currentSheet->setCellValueExplicit('C'.$cellIndex,"快递号不存在，无法更新运费", \PHPExcel_Cell_DataType::TYPE_STRING);
          
          $cellIndex++;
      }
      $zj = $is_sign + $is_sign2;
      $currentSheet->setCellValueExplicit('A'.$cellIndex,'统计', \PHPExcel_Cell_DataType::TYPE_STRING);
      $currentSheet->setCellValueExplicit('B'.$cellIndex,'总计:'.$zj.'条,'.'成功更新'.$is_sign.'条', \PHPExcel_Cell_DataType::TYPE_STRING);
      //输出
     
      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="' . 'ggg.xls' );
      header('Cache-Control: max-age=0');
      $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
      $objWriter->save('php://output');
      
  }
  
  public function yf(){
      $this->assign('src', '/yf/index.php');
      $this->display('index');
  
  }
  public function newgfexcel(){
      //$this->assign('src', '/gfexcel/newgfexcel.php');
      $this->display('newgfexcel');
  
  }

public function gfexcel(){
    $this->assign('src', '/gfexcel/gfexcel.php');
    $this->display('index');
    
}
public  function jhexcel(){
    $this->assign('src', '/jhexcel/jhexcel.php');
    $this->display('index'); 

}  


public function ydexcel(){

    $this->assign('src', '/ydexcel/ydexcel.php');
    $this->display('index');
}
public function ydexcel2(){

    $this->assign('src', '/ydexcel/ydexcel2.php');
    $this->display('index');
}

    private function httpPost($url ,$data,$timeoutMs=3000 ){
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_CONNECTTIMEOUT_MS, $timeoutMs);
    
        if (!empty($data)) {
            curl_setopt($ch, CURLOPT_POST, 1);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
        }
    
        $rs = curl_exec($ch);
    
        curl_close($ch); 
    
        return $rs;
    }
    
    /**
     * 默认格式
     * @return string
     */
    function getSapConfigStr(){
    $config=array();
       $config['content']='[
        {
            "header": "订单号 ",
            "name": "U_OrderId",
            "field": "U_OrderId",
            "width": "150px",
            "headerAlign": "left",
            "displayField": "U_OrderId",
            "_id": 1,
            "_index": 0,
            "summaryValue": ""
        },
        {
            "header": "当前订单状态",
            "field": "U_OStatus",
            "renderer": "orderstatus",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_index": 1,
            "summaryValue": ""
        },
        {
            "header": "订单优先级",
            "field": "U_YXJ",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 2,
            "_index": 2,
            "summaryValue": ""
        },
        {
            "header": "物料代码",
            "field": "U_ItemCode",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 4,
            "_index": 3,
            "summaryValue": ""
        },
        {
            "header": "颜色",
            "field": "U_Color",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 6,
            "_index": 4,
            "summaryValue": ""
        },
        {
            "header": "物料描述",
            "field": "U_ItemName",
            "width": "240px",
            "headerAlign": "center",
            "_id": 5,
            "_index": 5,
            "summaryValue": ""
        },
        {
            "header": "收货人\t\t \t\t",
            "field": "U_Cstname",
            "width": "50px",
            "headerAlign": "center",
            "allowSort": true,
            "editor": {
                "cls": "mini-textbox",
                "style": "width: 100%;",
                "type": "textbox"
            },
            "_id": 7,
            "_index": 6,
            "summaryValue": ""
        },
        {
            "header": "数量",
            "field": "U_Qty",
            "renderer": "onfixed",
            "width": "40px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 8,
            "_index": 7,
            "summaryValue": ""
        },
        {
            "header": "行类型",
            "field": "U_Type",
            "renderer": "line_type",
            "width": "40px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 9,
            "_index": 8,
            "summaryValue": ""
        },
        {
            "header": "结算价",
            "name": "U_NoDPrc",
            "field": "U_NoDPrc",
            "width": "80px",
            "headerAlign": "left",
            "displayField": "U_NoDPrc",
            "_id": 1,
            "_index": 0,
            "summaryValue": ""
        },
        {
            "header": "电话号码\t\t \t\t",
            "field": "U_Phone",
            "width": "130px",
            "headerAlign": "center",
            "allowSort": true,
            "editor": {
                "cls": "mini-textbox",
                "style": "width: 100%;",
                "type": "textbox"
            },
            "_id": 10,
            "_index": 9,
            "summaryValue": ""
        },
        {
            "header": "发票抬头\t\t \t\t",
            "field": "U_InHead",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "editor": {
                "cls": "mini-textbox",
                "style": "width: 100%;",
                "type": "textbox"
            },
            "_id": 11,
            "_index": 10,
            "summaryValue": ""
        },
        {
            "header": "地址\t\t \t\t \t\t",
            "field": "U_Address",
            "width": "300px",
            "headerAlign": "center",
            "allowSort": true,
            "editor": {
                "cls": "mini-textbox",
                "style": "width: 100%;",
                "type": "textbox"
            },
            "_id": 12,
            "_index": 11,
            "summaryValue": ""
        },
        {
            "header": "备注\t\t \t\t",
            "field": "U_Memo",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "editor": {
                "cls": "mini-textbox",
                "style": "width: 100%;",
                "type": "textbox"
            },
            "_id": 13,
            "_index": 12,
            "summaryValue": ""
        },
        {
            "header": "订单确认日期",
            "field": "U_IdenDate",
            "width": "80px",
            "headerAlign": "center",
            "dateFormat": "yyyy-MM-dd",
            "allowSort": true,
            "_id": 14,
            "_index": 13,
            "summaryValue": ""
        },
        {
            "header": "已发货时间",
            "width": "80px",
            "headerAlign": "center",
            "dateFormat": "yyyy-MM-dd HH:mm:ss",
            "allowSort": true,
            "_id": 15,
            "_index": 14,
            "summaryValue": ""
        },
        {
            "header": "是否代发",
            "field": "U_Replace",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 16,
            "_index": 15,
            "summaryValue": ""
        },
        {
            "header": "发票类型",
            "field": "U_InType",
            "renderer": "intype",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 17,
            "_index": 16,
            "summaryValue": ""
        },
        {
            "header": "是否拣配",
            "field": "U_IsPick",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 18,
            "_index": 17,
            "summaryValue": ""
        },
        {
            "header": "快递公司",
            "field": "U_ExpCom",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 19,
            "_index": 18,
            "summaryValue": ""
        },
        {
            "header": "快递号",
            "field": "U_ExpNo",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 20,
            "_index": 19,
            "summaryValue": ""
        },
        {
            "header": "发货序列号",
            "field": "U_SerNum",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "summaryValue": ""
        },
        {
            "header": "客户代码",
            "field": "U_CardCode",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 21,
            "_index": 20,
            "summaryValue": ""
        },
        {
            "header": "客户描述",
            "field": "U_CardName",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 22,
            "_index": 21,
            "summaryValue": ""
        },
        {
            "header": "产品经理",
            "field": "U_PManager",
            "width": "120px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 23,
            "_index": 22,
            "summaryValue": ""
        },
        {
            "header": "产品性质",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 24,
            "_index": 23,
            "summaryValue": ""
        },
        {
            "header": "买家姓名",
            "field": "U_Cstname",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 25,
            "_index": 24,
            "summaryValue": ""
        },
        {
            "header": "售前订单号",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 26,
            "_index": 25,
            "summaryValue": ""
        },
        {
            "header": "售后订单号",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 27,
            "_index": 26,
            "summaryValue": ""
        },
        {
            "header": "客户物料号",
            "field": "U_CitemCd",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 28,
            "_index": 27,
            "summaryValue": ""
        },
        {
            "header": "客户物料名称",
            "field": "U_CitemNm",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 29,
            "_index": 28,
            "summaryValue": ""
        },
        {
            "header": "总计",
            "field": "total",
            "renderer": "onfixed_2",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 30,
            "_index": 29,
            "summaryValue": ""
        },
        {
            "header": "单据日期",
            "field": "U_DocDate",
            "width": "80px",
            "headerAlign": "center",
            "dateFormat": "yyyy-MM-dd HH:mm:ss",
            "allowSort": true,
            "_id": 31,
            "_index": 30,
            "summaryValue": ""
        },
        {
            "header": "其他电话号码",
            "field": "U_OTPhone",
            "width": "130px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 32,
            "_index": 31,
            "summaryValue": ""
        },
        {
            "header": "导入单号",
            "field": "DocEntry",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 33,
            "_index": 32,
            "summaryValue": ""
        },
        {
            "header": "导入单行",
            "field": "LineId",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 34,
            "_index": 33,
            "summaryValue": ""
        },
        {
            "header": "商品型号",
            "field": "U_Model",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 35,
            "_index": 34,
            "summaryValue": ""
        },
        {
            "header": "省",
            "field": "U_State",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 36,
            "_index": 35,
            "summaryValue": ""
        },
        {
            "header": "市",
            "field": "U_City",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 37,
            "_index": 36,
            "summaryValue": ""
        },
        {
            "header": "县/区",
            "field": "U_County",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 38,
            "_index": 37,
            "summaryValue": ""
        },
        {
            "header": "订单取消原因",
            "field": "U_IsClose",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 39,
            "_index": 38,
            "summaryValue": ""
        },
        {
            "header": "订单取消备注",
            "field": "U_ClsMemo",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 40,
            "_index": 39,
            "summaryValue": ""
        },
        {
            "header": "开票备注",
            "field": "U_InvMemo",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 41,
            "_index": 40,
            "summaryValue": ""
        },
        {
            "header": "快递费",
            "field": "U_ExpCost",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 42,
            "_index": 41,
            "summaryValue": ""
        },
        {
            "header": "核重",
            "field": "U_Weight",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 43,
            "_index": 42,
            "summaryValue": ""
        },
        {
            "header": "发票号",
            "field": "U_InNo",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 44,
            "_index": 43,
            "summaryValue": ""
        },
        {
            "header": "开票日期",
            "field": "U_InDate",
            "width": "80px",
            "headerAlign": "center",
            "dateFormat": "yyyy-MM-dd HH:mm:ss",
            "allowSort": true,
            "_id": 45,
            "_index": 44,
            "summaryValue": ""
        },
        {
            "header": "财务是否开票",
            "field": "U_IsKP",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 46,
            "_index": 45,
            "summaryValue": ""
        },
        {
            "header": "发票已寄出",
            "field": "U_IsInvD",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 47,
            "_index": 46,
            "summaryValue": ""
        },
        {
            "header": "仓库代码",
            "field": "U_WhsCode",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 48,
            "_index": 47,
            "summaryValue": ""
        },
        {
            "header": "发货仓库",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 49,
            "_index": 48,
            "summaryValue": ""
        },
        {
            "header": "仓库名称",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 50,
            "_index": 49,
            "summaryValue": ""
        },
        {
            "header": "发货仓库名称",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 51,
            "_index": 50,
            "summaryValue": ""
        },
        {
            "header": "客服代码",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 52,
            "_index": 51,
            "summaryValue": ""
        },
        {
            "header": "客服名称",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 53,
            "_index": 52,
            "summaryValue": ""
        },
        {
            "header": "行状态",
            "field": "U_LineSts",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 54,
            "_index": 53,
            "summaryValue": ""
        },
        {
            "header": "已发签收短信时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 55,
            "_index": 54,
            "summaryValue": ""
        },
        {
            "header": "确认收货时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 56,
            "_index": 55,
            "summaryValue": ""
        },
        {
            "header": "已发货待取消时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 57,
            "_index": 56,
            "summaryValue": ""
        },
        {
            "header": "丢件/损坏时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 58,
            "_index": 57,
            "summaryValue": ""
        },
        {
            "header": "投诉时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 59,
            "_index": 58,
            "summaryValue": ""
        },
        {
            "header": "处理时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 60,
            "_index": 59,
            "summaryValue": ""
        },
        {
            "header": "售后服务类型",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 61,
            "_index": 60,
            "summaryValue": ""
        },
        {
            "header": "投诉原因",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 62,
            "_index": 61,
            "summaryValue": ""
        },
        {
            "header": "投诉结果",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 63,
            "_index": 62,
            "summaryValue": ""
        },
        {
            "header": "处理过程",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 64,
            "_index": 63,
            "summaryValue": ""
        },
        {
            "header": "投诉咨询",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 65,
            "_index": 64,
            "summaryValue": ""
        }
    ]';
        
        return $config;
         
    }
    
    
    
    function getSapConfigStrBysc(){
        $config=array();
        $config['content']='[
        {
            "header": "订单号 ",
            "name": "U_OrderId",
            "field": "U_OrderId",
            "width": "150px",
            "headerAlign": "left",
            "displayField": "U_OrderId",
            "_id": 1,
            "_index": 0,
            "summaryValue": ""
        },
        {
            "header": "当前订单状态",
            "field": "U_OStatus",
            "renderer": "orderstatus",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_index": 1,
            "summaryValue": ""
        },
        {
            "header": "订单优先级",
            "field": "U_YXJ",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 2,
            "_index": 2,
            "summaryValue": ""
        },
        {
            "header": "物料代码",
            "field": "U_ItemCode",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 4,
            "_index": 3,
            "summaryValue": ""
        },
        {
            "header": "颜色",
            "field": "U_Color",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 6,
            "_index": 4,
            "summaryValue": ""
        },
        {
            "header": "物料描述",
            "field": "U_ItemName",
            "width": "240px",
            "headerAlign": "center",
            "_id": 5,
            "_index": 5,
            "summaryValue": ""
        },

        {
            "header": "数量",
            "field": "U_Qty",
            "renderer": "onfixed",
            "width": "40px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 8,
            "_index": 7,
            "summaryValue": ""
        },
        {
            "header": "行类型",
            "field": "U_Type",
            "renderer": "line_type",
            "width": "40px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 9,
            "_index": 8,
            "summaryValue": ""
        },
        {
            "header": "结算价",
            "name": "U_NoDPrc",
            "field": "U_NoDPrc",
            "width": "80px",
            "headerAlign": "left",
            "displayField": "U_NoDPrc",
            "_id": 1,
            "_index": 0,
            "summaryValue": ""
        },
  
        {
            "header": "发票抬头\t\t \t\t",
            "field": "U_InHead",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "editor": {
                "cls": "mini-textbox",
                "style": "width: 100%;",
                "type": "textbox"
            },
            "_id": 11,
            "_index": 10,
            "summaryValue": ""
        },
     
        {
            "header": "备注\t\t \t\t",
            "field": "U_Memo",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "editor": {
                "cls": "mini-textbox",
                "style": "width: 100%;",
                "type": "textbox"
            },
            "_id": 13,
            "_index": 12,
            "summaryValue": ""
        },
        {
            "header": "订单确认日期",
            "field": "U_IdenDate",
            "width": "80px",
            "headerAlign": "center",
            "dateFormat": "yyyy-MM-dd",
            "allowSort": true,
            "_id": 14,
            "_index": 13,
            "summaryValue": ""
        },
        {
            "header": "已发货时间",
            "width": "80px",
            "headerAlign": "center",
            "dateFormat": "yyyy-MM-dd HH:mm:ss",
            "allowSort": true,
            "_id": 15,
            "_index": 14,
            "summaryValue": ""
        },
        {
            "header": "是否代发",
            "field": "U_Replace",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 16,
            "_index": 15,
            "summaryValue": ""
        },
        {
            "header": "发票类型",
            "field": "U_InType",
            "renderer": "intype",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 17,
            "_index": 16,
            "summaryValue": ""
        },
        {
            "header": "是否拣配",
            "field": "U_IsPick",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 18,
            "_index": 17,
            "summaryValue": ""
        },
        {
            "header": "快递公司",
            "field": "U_ExpCom",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 19,
            "_index": 18,
            "summaryValue": ""
        },
        {
            "header": "快递号",
            "field": "U_ExpNo",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 20,
            "_index": 19,
            "summaryValue": ""
        },
        {
            "header": "发货序列号",
            "field": "U_SerNum",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "summaryValue": ""
        },
        {
            "header": "客户代码",
            "field": "U_CardCode",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 21,
            "_index": 20,
            "summaryValue": ""
        },
        {
            "header": "客户描述",
            "field": "U_CardName",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 22,
            "_index": 21,
            "summaryValue": ""
        },
        {
            "header": "产品经理",
            "field": "U_PManager",
            "width": "120px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 23,
            "_index": 22,
            "summaryValue": ""
        },
        {
            "header": "产品性质",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 24,
            "_index": 23,
            "summaryValue": ""
        },
     
        {
            "header": "售前订单号",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 26,
            "_index": 25,
            "summaryValue": ""
        },
        {
            "header": "售后订单号",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 27,
            "_index": 26,
            "summaryValue": ""
        },
        {
            "header": "客户物料号",
            "field": "U_CitemCd",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 28,
            "_index": 27,
            "summaryValue": ""
        },
        {
            "header": "客户物料名称",
            "field": "U_CitemNm",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 29,
            "_index": 28,
            "summaryValue": ""
        },
        {
            "header": "总计",
            "field": "total",
            "renderer": "onfixed_2",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 30,
            "_index": 29,
            "summaryValue": ""
        },
        {
            "header": "单据日期",
            "field": "U_DocDate",
            "width": "80px",
            "headerAlign": "center",
            "dateFormat": "yyyy-MM-dd HH:mm:ss",
            "allowSort": true,
            "_id": 31,
            "_index": 30,
            "summaryValue": ""
        },
    
        {
            "header": "导入单号",
            "field": "DocEntry",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 33,
            "_index": 32,
            "summaryValue": ""
        },
        {
            "header": "导入单行",
            "field": "LineId",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 34,
            "_index": 33,
            "summaryValue": ""
        },
        {
            "header": "商品型号",
            "field": "U_Model",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 35,
            "_index": 34,
            "summaryValue": ""
        },
        {
            "header": "省",
            "field": "U_State",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 36,
            "_index": 35,
            "summaryValue": ""
        },
        {
            "header": "市",
            "field": "U_City",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 37,
            "_index": 36,
            "summaryValue": ""
        },
        {
            "header": "县/区",
            "field": "U_County",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 38,
            "_index": 37,
            "summaryValue": ""
        },
        {
            "header": "订单取消原因",
            "field": "U_IsClose",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 39,
            "_index": 38,
            "summaryValue": ""
        },
        {
            "header": "订单取消备注",
            "field": "U_ClsMemo",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 40,
            "_index": 39,
            "summaryValue": ""
        },
        {
            "header": "开票备注",
            "field": "U_InvMemo",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 41,
            "_index": 40,
            "summaryValue": ""
        },
        {
            "header": "快递费",
            "field": "U_ExpCost",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 42,
            "_index": 41,
            "summaryValue": ""
        },
        {
            "header": "核重",
            "field": "U_Weight",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 43,
            "_index": 42,
            "summaryValue": ""
        },
        {
            "header": "发票号",
            "field": "U_InNo",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 44,
            "_index": 43,
            "summaryValue": ""
        },
        {
            "header": "开票日期",
            "field": "U_InDate",
            "width": "80px",
            "headerAlign": "center",
            "dateFormat": "yyyy-MM-dd HH:mm:ss",
            "allowSort": true,
            "_id": 45,
            "_index": 44,
            "summaryValue": ""
        },
        {
            "header": "财务是否开票",
            "field": "U_IsKP",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 46,
            "_index": 45,
            "summaryValue": ""
        },
        {
            "header": "发票已寄出",
            "field": "U_IsInvD",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 47,
            "_index": 46,
            "summaryValue": ""
        },
        {
            "header": "仓库代码",
            "field": "U_WhsCode",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 48,
            "_index": 47,
            "summaryValue": ""
        },
        {
            "header": "发货仓库",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 49,
            "_index": 48,
            "summaryValue": ""
        },
        {
            "header": "仓库名称",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 50,
            "_index": 49,
            "summaryValue": ""
        },
        {
            "header": "发货仓库名称",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 51,
            "_index": 50,
            "summaryValue": ""
        },
        {
            "header": "客服代码",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 52,
            "_index": 51,
            "summaryValue": ""
        },
        {
            "header": "客服名称",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 53,
            "_index": 52,
            "summaryValue": ""
        },
        {
            "header": "行状态",
            "field": "U_LineSts",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 54,
            "_index": 53,
            "summaryValue": ""
        },
        {
            "header": "已发签收短信时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 55,
            "_index": 54,
            "summaryValue": ""
        },
        {
            "header": "确认收货时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 56,
            "_index": 55,
            "summaryValue": ""
        },
        {
            "header": "已发货待取消时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 57,
            "_index": 56,
            "summaryValue": ""
        },
        {
            "header": "丢件/损坏时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 58,
            "_index": 57,
            "summaryValue": ""
        },
        {
            "header": "投诉时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 59,
            "_index": 58,
            "summaryValue": ""
        },
        {
            "header": "处理时间",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 60,
            "_index": 59,
            "summaryValue": ""
        },
        {
            "header": "售后服务类型",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 61,
            "_index": 60,
            "summaryValue": ""
        },
        {
            "header": "投诉原因",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 62,
            "_index": 61,
            "summaryValue": ""
        },
        {
            "header": "投诉结果",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 63,
            "_index": 62,
            "summaryValue": ""
        },
        {
            "header": "处理过程",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 64,
            "_index": 63,
            "summaryValue": ""
        },
        {
            "header": "投诉咨询",
            "width": "80px",
            "headerAlign": "center",
            "allowSort": true,
            "_id": 65,
            "_index": 64,
            "summaryValue": ""
        }
    ]';
    
        return $config;
         
    }

    //************************************//
    // MARK: Update on 20170420
    public function newgfuploadexcel(){
        $time =time();

        // excel表头 与 SAP的对应关系
        $orderHeadArray = array(

            '订单编号' =>'U_OrderId', //B
            '商品编码' =>'U_CitemCd', //L
            '商品名称'=>'U_CitemNm', //M
            '商品价格（广发售价）'=>'U_Price', //N
            '数量'=>'U_Qty', //Q
            '邮购分期类别码'=>'U_FQQTY', //O
            //'购买期数'=>'U_FQQTY', //R
            '现金支付金额'=>'U_GoodsFee', //S
            '属性值1'=>'U_Color', //X
            '发票抬头'=>'U_InHead',//AC
            '收货人'=>'U_Cstname', //AD
            '收货人电话1'=>'U_Phone', //AE
            '收货人电话2'=>'U_OTPhone', //AF
            '收货地址'=>'U_Address', //AG
            '收货人邮编'=>'U_zipcode', //AH
            '用户留言'=>'U_Memo', //AJ
            '送货时间要求'=>'U_DelTime', //AL
            '属性值2'=>'U_Model', //Z

            '属性名1'=>'PName1', //W
            '属性名2'=>'PName2' //Y

        );

        $html = "";
        $errorlist = array();
        $echoStr = '';
        $cellIndex = 5;
        ini_set("max_execution_time", "1800");
        import("@.ORG.Util.Sqlserver");
        $db = new Sqlserver();
        //Change to PDO_DBLIB
        //$db = new PDO_DBLIB_MSSQL();
        if (!empty($_FILES)) {
            import("@.ORG.UploadFile");
            $config=array(
                'allowExts'=>array('xlsx','xls'),
                'savePath'=>'./exceltojh/',
                'saveRule'=>'time',
            );
            $upload = new UploadFile($config);
            if (!$upload->upload($config['savePath'])) {
                $this->error($upload->getErrorMsg());
            } else {
                $is_sign = 0;
                $info = $upload->getUploadFileInfo();
                $name = $info[0]['savename'];
                Vendor('Excel.PHPExcel');
                $filename= $config['savePath'].$name;
                // echo $filename;exit;
                //导入thinkphp第三方类库
                $objPHPExcel = \PHPExcel_IOFactory::load($filename);

                $sheetData   = $objPHPExcel -> getActiveSheet() -> toArray(null, true, true, true);

                $currentSheet = $objPHPExcel->getSheet(0);
                //echo $currentSheet;exit;
                $allColumn = $currentSheet->getHighestColumn();//取到最大列数
                $allRow = $currentSheet->getHighestRow();//取到最大行数
                //echo $allRow;exit;

                // excel 中表头
                $excelHeadCN = $sheetData[4];



                $excelHead = array();
                foreach ($excelHeadCN as $key => $value){
                    //$excelHead[$orderHeadArray[$value]] = $key;
                    $excelHead[$key] = $orderHeadArray[$value];
                }
                unset($excelHead['']);
                foreach ($excelHead as $key => $value)
                {
                    if (empty($value)){
                        unset($excelHead[$key]);
                    }
                }
                // SAP表头与Excel列对应关系
                //var_dump($excelHead);

                unset($sheetData[1],$sheetData[2],$sheetData[3],$sheetData[4]); //删除excel表头 列名等行数据
//              foreach ($sheetData as $key => $v) {
//                  if(!$v) continue;
//                  $tempArr =array();
//                  $patterns = '￥';
//                  $replacements = '';
//                  //$tempArr['Xh'] = trim($v['A']);
//                  $tempArr['U_OrderId'] = trim($v['B']);
//                  $tempArr['U_CitemCd'] = trim($v['L']);
//                  $tempArr['U_CitemNm'] = trim($v['M']);
//                  $tempArr['U_Price'] = trim(str_replace($patterns, $replacements, $v['N']));
//
//                     // $v['E']);
//                  $tempArr['U_Qty'] = trim($v['Q']);
//                  $tempArr['U_FQQTY'] = trim($v['R']);
//                  $tempArr['U_GoodsFee'] = trim(str_replace($patterns, $replacements, $v['S']));
//                  $tempArr['U_Color'] = trim($v['X']);
//                  $tempArr['U_Cstname'] = trim($v['AD']);
//                  $tempArr['U_Phone'] = trim($v['AE']);
//                  $tempArr['U_OTPhone'] = trim($v['AF']);
//                  if(empty($v['AE'])&&!empty($v['AF'])){
//                      $tempArr['U_Phone'] = trim($v['AF']);
//                  }
//                  $tempArr['U_Address'] = trim($v['AG']);
//                  $tempArr['U_zipcode'] = trim($v['AH']);
//                  $tempArr['U_Memo'] = trim($v['AJ']);
//
//                //  $sql2="select U_Is3C from [@ti_z0041] where U_CItemCode='{$v['L']}'";
//                 // $is3C=$db->query($sql2)[0]['U_Is3C'];
//
//                  if(strstr($v['O'],"12TY")){
//                      $tempArr['U_DisCt'] = trim(0.08*$tempArr['U_Price']);
//                  }
//                  else if(strstr($v['O'],"18TY")){
//
//                      $tempArr['U_DisCt'] = trim(0.09*$tempArr['U_Price']);
//
//                  }
//                  else if(strstr($v['O'],"24TY")){
//
//                      $tempArr['U_DisCt'] = trim(0.1*$tempArr['U_Price']);
//
//                  }
//                  else if(strstr($v['O'],"0005")){
//
//                      $tempArr['U_DisCt'] = trim(0.05*$tempArr['U_Price']);
//
//                  }
//                  else if(strstr($v['O'],"0007")){
//
//                          $tempArr['U_DisCt'] = trim(0.07*$tempArr['U_Price']);
//
//
//                  }
//                  else if(strstr($v['O'],"0003")){
//
//                          $tempArr['U_DisCt'] = trim(0.03*$tempArr['U_Price']);
//
//                  }
//                  else if(strstr($v['O'],"01TY")){
//
//                          $tempArr['U_DisCt'] = trim(0.03*$tempArr['U_Price']);
//                  }
//                  else if(strstr($v['O'],"05TS")){
//
//                          $tempArr['U_DisCt'] = trim(0.005*$tempArr['U_Price']);
//
//                  }
//
//                  $tempArr['U_DocDate'] = date("Y-m-d H:i:s",strtotime(substr($v['B'], 0,8)));
//                  $tempArr['U_Total'] =  trim($v['Q'])*trim(str_replace($patterns, $replacements, $v['N']));
//                  $tempArr['U_InptCode'] = "T013";
//                  $tempArr['U_CardCode'] = "C20020";
//                  $tempArr['U_Bat'] = $time;
//                 // error_log(var_export($tempArr,1));
//                 // continue;
//                       $str = "'".implode("','", $tempArr)."'";
//
//                      $sql =" insert into [@Auto_Import]  (" .implode(',', array_keys($tempArr) ) .") values({$str})" ;
//                      // 这里的转换不需要，移入 saveBySQL 方法
//                      //$sql = iconv('utf-8', 'gbk', $sql) ;
//                      $saveres = $db->saveBySQL($sql);
//
//              }
                // exit;

                $mergeStr = '';
                $countNum = 0;

                foreach ($sheetData as $key => $v) {
                    //数据 $key行号 $v数组

                    $targetData = array();
                    foreach ($v as $key => $value)
                    {
                        //$targetData[$excelHead[$key]] = $value;
                        if ($excelHead[$key] == 'U_Price' || $excelHead[$key] == 'U_GoodsFee'){
                            // 转换U_Price
                            $patterns = '￥';
                            $replacements = '';
                            $targetData[$excelHead[$key]] = trim(str_replace($patterns, $replacements, $value));;
                        }
                        else{
                            $targetData[$excelHead[$key]] = trim($value);
                        }
                    }
                    unset($targetData['']);

                  // 计算费率
                  if(strstr($targetData['U_FQQTY'],"12TY")){
                        $targetData['U_DisCt'] = trim(0.08*$targetData['U_Price']);
                  }
                  else if(strstr($targetData['U_FQQTY'],"18TY")){
                      $targetData['U_DisCt'] = trim(0.09*$targetData['U_Price']);
                  }
                  else if(strstr($targetData['U_FQQTY'],"24TY")){
                      $targetData['U_DisCt'] = trim(0.1*$targetData['U_Price']);
                  }
                  else if(strstr($targetData['U_FQQTY'],"0005")){
                      $targetData['U_DisCt'] = trim(0.05*$targetData['U_Price']);
                  }
                  else if(strstr($targetData['U_FQQTY'],"0007")){
                      $targetData['U_DisCt'] = trim(0.07*$targetData['U_Price']);
                  }
                  else if(strstr($targetData['U_FQQTY'],"0003")){
                      $targetData['U_DisCt'] = trim(0.03*$targetData['U_Price']);
                  }
                  else if(strstr($targetData['U_FQQTY'],"01TY")){
                      $targetData['U_DisCt'] = trim(0.03*$targetData['U_Price']);
                  }
                  else if(strstr($targetData['U_FQQTY'],"05TS")){
                      $targetData['U_DisCt'] = trim(0.005*$targetData['U_Price']);
                  }
                  else if(strstr($targetData['U_FQQTY'],"0001")){
                      $targetData['U_DisCt'] = trim(0.03*$targetData['U_Price']);
                  } // 2017-06-09 添加新规则：0001 信息来源：austin
                  else if(strstr($targetData['U_FQQTY'],"0031")){
                      $targetData['U_DisCt'] = trim(0.03*$targetData['U_Price']);
                  } // 2017-08-08 添加新规则：0031 信息来源：austin
                  else if(strstr($targetData['U_FQQTY'],"0004")){
                      $targetData['U_DisCt'] = trim(0.04*$targetData['U_Price']);
                  } // 2017-08-08 添加新规则：0004 信息来源：austin

                    // 补全数据
                    $targetData['U_DocDate'] = date("Y-m-d H:i:s",strtotime(substr($targetData['U_OrderId'], 0,8)));
                    $targetData['U_Total'] =  trim($targetData['U_Qty'])*trim(str_replace($patterns, $replacements, $targetData['U_Price']));
                    
                    $targetData['U_InptCode'] = "T013";

                    // 导入失败的情况: U_CardCode为空, U_Price没有作转换
                    $targetData['U_CardCode'] = "C20020"; //测试插入失败

                    $targetData['U_Bat'] = $time;
                    //var_dump($targetData);

                    /**2017-05-05需求
                    Resource:
                    属性名1 A
                    属性值1 B
                    ,
                    属性名2 C
                    属性值2 D
                    VALUE -> U_Color
                    Architecture:
                    if A == 颜色 THEN VALUE = B
                    else
                    if C == 颜色 THEN VALUE = D
                    else THEN VALUE = B*/
                    if($targetData['PName1'] == '颜色')
                    {
                        //$targetData['U_Color'] = $targetData['U_Color'];
                    }else{
                        $tmpStoreModel = $targetData['U_Color'];
                        if($targetData['PName2'] == '颜色')
                        {
                            //U_Model
                            $targetData['U_Color'] = $targetData['U_Model'];
                        }else{
                            //$targetData['U_Color'] = $targetData['U_Color'];
                        }

                        //for U_model
                        $targetData['U_Model'] = $tmpStoreModel;
                    }
                    //移除判断元素
                    unset($targetData['PName1']);
                    unset($targetData['PName2']);
                    //echo "<hr>";
                    //var_dump($targetData);

                    //20170508地址中出现单引号,替换为\
                    $targetData['U_Address'] = str_replace("'","\\",$targetData['U_Address']);

                    $str = "'".implode("','", $targetData)."'";

                    $countNum++;

                    if ($countNum == 1) $mergeStr .= "({$str})";
                    else $mergeStr .= ", ({$str})";
                    //var_dump($mergeStr);

                    //$sql =" insert into [@Auto_Import]  (" .implode(',', array_keys($targetData) ) .") values({$str})" ;
                    //$saveres = $db->saveBySQL($sql);

                }

                $sql =" insert into [@Auto_Import]  (" .implode(',', array_keys($targetData) ) .") values $mergeStr";
                //echo $sql;
                //die();
                $sql= mb_convert_encoding($sql, "GBK", "UTF-8");
                $result = $db->saveBysql($sql);

                $db->close();

                if ($result)
                $this->assign('importResult', 'YES');
                else
                $this->assign('importResult', 'NO');

                $this->display('newgfuploadexcel');

            }

         
        }





    }

    public function errorhistoryguangfa()
    {
        import("@.ORG.Util.Sqlserver");
        $db = new Sqlserver();
        $sql ="select U_Cardcode,U_DocDate,U_CitemCd,U_CitemNm,U_Color,U_Model,count(1) as U_Count ,ErrorMessage,ErrorTime from [@Auto_Import] T0 left join [@Error_Message] T1 on t0.KeyCode=t1.KeyEntry where U_IsImport is null group by U_Cardcode,U_CitemCd,U_CitemNm,U_Color,U_Model,U_DocDate,ErrorMessage,ErrorTime";
     
        $data =$db->query($sql);
        $db->close();

        if ($data)
            $this->assign('hasErrors', 'YES');
        else
            $this->assign('hasErrors', 'NO');

        $this->display('errorhistoryguangfa');
    }

    public function activesap()
    {
        //activetype: 1:download_excel, 2:active_import
        $activeType = I("activetype");
        import("@.ORG.Util.Sqlserver");
        $db = new Sqlserver();
        if ($activeType == 1)
        {

            // fetch data:
           
            $sql ="select U_Cardcode,U_DocDate,U_CitemCd,U_CitemNm,U_Color,U_Model,count(1) as U_Count ,ErrorMessage,ErrorTime from [@Auto_Import] T0 left join [@Error_Message] T1 on t0.KeyCode=t1.KeyEntry where U_IsImport is null group by U_Cardcode,U_CitemCd,U_CitemNm,U_Color,U_Model,U_DocDate,ErrorMessage,ErrorTime";
            $data = $db->query($sql);
            //var_dump($data);die();

            Vendor('Excel.PHPExcel');
            $objPHPExcel = new \PHPExcel();
            $currentSheet = $objPHPExcel->getSheet(0);
            $m = 1;
            $currentSheet->setCellValue('A'.$m, 'U_Cardcode');
            $currentSheet->setCellValue('B'.$m, 'U_DocDate');
            $currentSheet->setCellValue('C'.$m, 'U_CitemCd');
            $currentSheet->setCellValue('D'.$m, 'U_CitemNm');
            $currentSheet->setCellValue('E'.$m, 'U_Color');
            $currentSheet->setCellValue('F'.$m, 'U_Model');
            $currentSheet->setCellValue('G'.$m, 'U_Count');
            $currentSheet->setCellValue('H'.$m, 'ErrorMessage');
            $currentSheet->setCellValue('I'.$m, 'ErrorTime');
            $cellIndex = 1;
            foreach ($data as $key => $value){

                $cellIndex++;

                //var_dump($value['U_Cardcode']);die();
                //mb_convert_encoding($value['ErrorTime'],"UTF-8","GBK")

                $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['U_Cardcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('B'.$cellIndex,$value['U_DocDate'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('C'.$cellIndex,$value['U_CitemCd'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('D'.$cellIndex,$value['U_CitemNm'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('E'.$cellIndex,$value['U_Color'],\PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('F'.$cellIndex,$value['U_Model'],\PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('G'.$cellIndex,$value['U_Count'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('H'.$cellIndex,$value['ErrorMessage'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('I'.$cellIndex,$value['ErrorTime'], \PHPExcel_Cell_DataType::TYPE_STRING);

            }

            $name = uniqid().'.xls';
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="' . $name );
            header('Cache-Control: max-age=0');
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');

        }
        elseif ($activeType == 2)
        {
            // 触发执行存储进程
            //$time = '1492738484'; //the date
            //$sql="[dbo].[Z0_AutoImport] '{$time}'";

            $sql="[dbo].[Z0_AutoImportAllopen]";
          
            $result = $db->query($sql);
            error_log('执行存储过程'.var_export($result,1));

            if ($result)
                $this->assign('runResult', 'YES');
            else
                $this->assign('runResult', 'NO');

        }

        $this->assign('activetype', $activeType);
        $this->display('activesap');

    }

    public function getlifecycle()
    {
        $orderid = I('orderid');
        $DocEntry = I('DocEntry');
        $LineId = I('LineId');
        $this->assign('orderid',$orderid);
        $this->assign('DocEntry',$DocEntry);
        $this->assign('LineId',$LineId);
        $this->display();
    }

    public function dogetlifecycle()
    {
        import("@.ORG.Util.LifecycleDatabase");
        $lifecycleDatabase = LifecycleDatabase::getInstance();
        $model = $lifecycleDatabase->getModel();

        $orderid = $_REQUEST['orderid'];
        $DocEntry = $_REQUEST['DocEntry'];
        $LineId = $_REQUEST['LineId'];

        $sortOrder = I('sortOrder');
        $sortField = I('sortField')?I('sortField').' '.$sortOrder:" OpSyDealDateTime desc, OpSyStatus desc ";
        $pageIndex = I('pageIndex');
        $pageSize = I('pageSize')?:50;
        $where=' ';
        //$where.=" AND OrderID = '{$orderid}'";
        $where .= "(SourceTypeId='1' and OpSySID='".$DocEntry."~".$LineId."') or (SourceTypeId='2' and OrderID='".$orderid."')";
        //echo $where;die();
        $total =$model->db(1)->table("lc_opsysrecords")->where($where)->order($sortField)->count();
        $res = $model->db(1)->table("lc_opsysrecords")->where($where)->order($sortField)->limit($pageIndex*$pageSize,$pageSize)->select();
        echo json_encode(array('total'=>$total,'data'=>$res));exit;

    }

    public function showloginlog()
    {
        $this->display();
    }

    public function getloginlogdata()
    {
        $emp_no = I('key');
        $loginLogModel = M('loginLog');
        $pageIndex = I('pageIndex');
        $pageSize = I('pageSize')?:50;
        $where='1 = 1 ';
        if ($emp_no) $where.=" AND emp_no like '%{$emp_no}%'";
        $total =$loginLogModel->where($where)->order("id desc")->count();
        $res = $loginLogModel->where($where)->order("id desc")->limit($pageIndex*$pageSize,$pageSize)->select();
        echo json_encode(array('total'=>$total,'data'=>$res));exit;

    }


    /**
     * 2017-09-29 报表直接导出Excel: 运营部报表 > 毛利报表 -1
     */
    public function profitreportxexcel(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $this->assign("userInfo",$userInfo);

        //Export to Excel:

        $Cardcodes = $_POST['Cardcode'];
        $CardcodeSqlFormate="'".implode("','",$Cardcodes)."'";
        $FromDate = $_POST['FromDate'];
        $ToDate = $_POST['ToDate'];
        $ViewOption = $_POST['ViewOption'];

        if ( $ViewOption=='Total') {
            $sqlquery="select  T0.ITEMCODE,ItemName,Sum(Quantity) sumqty, null CardCode,
sum(profit) profit,
sum(linetotal) sumprice,sum(calclineStockprice) sumstockprice,
sum(bankcharge) sumbankfee ,
sum(calcexpressfee) sumexpress ,
sum(giftcount) sumgiftprice,
sum(giftprice) sumgift ,
sum(giftaddexpressfee) sumgiftaddexpressfee, sum(addexpressfee) sumaddstockfee,sum(calctax) sumtax   from profit_report T0 
where FreeTxt in (select OrderId from sales_order WHERE  DocDate>='{$FromDate}' and DocDate<='{$ToDate}' and cardcode in ($CardcodeSqlFormate) )
  GROUP by ITEMCODE,ItemName  order by itemcode";}
        if ( $ViewOption=='Everyone') {
            $sqlquery="select  T0.ITEMCODE,ItemName,Sum(Quantity) sumqty, CardCode,
sum(profit) profit,
sum(linetotal) sumprice,sum(calclineStockprice) sumstockprice,
sum(bankcharge) sumbankfee ,
sum(calcexpressfee) sumexpress ,
sum(giftcount) sumgiftprice,
sum(giftprice) sumgift ,
sum(giftaddexpressfee) sumgiftaddexpressfee, sum(addexpressfee) sumaddstockfee,sum(calctax) sumtax   from profit_report T0 
where FreeTxt in (select OrderId from sales_order WHERE  DocDate>='{$FromDate}' and DocDate<='{$ToDate}' and  cardcode in ($CardcodeSqlFormate)  )
  GROUP by ITEMCODE,ItemName,CardCode  order by itemcode";}

  //echo $sqlquery;die();

        import("@.ORG.Util.PermissionDatabase");
        $permissionDatabase = PermissionDatabase::getInstance();
        $model = $permissionDatabase->getModel();

        $data = $model->db(1)->query($sqlquery);
        //var_dump($data);die();

        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '物料号');
        $currentSheet->setCellValue('B'.$m, '物料名');
        $currentSheet->setCellValue('C'.$m, '数量');
        $currentSheet->setCellValue('D'.$m, '毛利');
        $currentSheet->setCellValue('E'.$m, '销售金额');

        $currentSheet->setCellValue('F'.$m, '库存价值');
        $currentSheet->setCellValue('G'.$m, '银行费用');
        $currentSheet->setCellValue('H'.$m, '快递费用');
        $currentSheet->setCellValue('I'.$m, '赠品件数');
        $currentSheet->setCellValue('J'.$m, '赠品成本');

        $currentSheet->setCellValue('K'.$m, '赠品运费');
        $currentSheet->setCellValue('L'.$m, '物流附加');
        $currentSheet->setCellValue('M'.$m, '税');

        if ( $ViewOption=="Everyone")
        {
            $currentSheet->setCellValue('N'.$m, '平台');
        }

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['ITEMCODE'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$value['ItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,round($value['sumqty'],0), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,round($value['profit'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,round($value['sumprice'],2),\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,round($value['sumstockprice'],2),\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,round($value['sumbankfee'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,round($value['sumexpress'],2),\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,round($value['sumgift'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,round($value['sumgiftprice'],2), \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,round($value['sumgiftaddexpressfee'],2),\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,round($value['sumaddstockfee'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,round($value['sumtax'],2), \PHPExcel_Cell_DataType::TYPE_STRING);

            if ( $ViewOption=="Everyone")
            {
                $currentSheet->setCellValueExplicit('N'.$cellIndex,$value['CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            }
        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    /**
     * 2017-09-29 报表直接导出Excel: 运营部报表 > 平台分析表 -2
     */
    public function classicreportx3excel(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);

        $FromDate=date("m/",time())."01".date("/Y",time());
        if(!empty($_GET['FromDate'])){ $FromDate=$_GET["FromDate"]; }
        $ToDate=date("m/d/Y", strtotime("+1 months", strtotime(date("m/",time())."01".date("/Y",time()))));
        if(!empty($_GET['ToDate'])){ $ToDate=$_GET["ToDate"]; }
        $selectdeptidden="ALL";

        //Export to Excel:
        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();

        $sqlquery="select  t0.Cardcode,dbo.BPcontact(t0.Cardcode,'CardName') CardName,SUM(Quantity) SUMQTY,SUM(linetotal) SUMPRICE ,sum(isnull([linetotal],0)-isnull([bankcharge],0)-isnull([calclineStockprice],0)-isnull([calcexpressfee],0)-isnull([addexpressfee],0)-isnull([giftcount],0)-isnull([giftaddexpressfee],0)-isnull([calctax],0)) PROFIT,sum([calctax]) SUMTAX from ax_reportx T0 left join oitm T1 ON T0.Itemcode=T1.itemcode where t0.docdate>='".$FromDate."' and t0.docdate<='".$ToDate."' ";
        $sqlquery=$sqlquery." group by  t0.CardCode";

        $data = $sqlserver->query($sqlquery);
        //var_dump($data);die();
        $sqlserver->close();

        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '平台');
        $currentSheet->setCellValue('B'.$m, '数量');
        $currentSheet->setCellValue('C'.$m, '销售金额');
        $currentSheet->setCellValue('D'.$m, '销售毛利');
        $currentSheet->setCellValue('E'.$m, '毛利率%');

        $currentSheet->setCellValue('F'.$m, '税总计');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,round($value['SUMQTY'],0), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,round($value['SUMPRICE'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,round($value['PROFIT'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,round($value['PROFIT']/$value['SUMPRICE']*100,2),\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,round($value['SUMTAX'],2),\PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    /**
     * 2017-09-29 报表直接导出Excel: 运营部报表 > 分平台销售趋势 -3
     */
    public function bptrendsxexcel(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);

        //Export to Excel:
        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();

        $cardcode = $_REQUEST['cardcode'];
//        $Cardcodes = $_REQUEST['Cardcode'];
//        $CardcodeSqlFormate="'".implode("','",$Cardcodes)."'";

        $sqlquery="select left(CONVERT(varchar(100), t0.docdate, 111),7) month ,SUM(Quantity) SUMQTY,SUM(linetotal) SUMPRICE ,sum(profit) PROFIT
 from AX_ReportXlite T0 left join  OITM t1 on T0.itemcode=t1.itemcode 
where T0.DOCDATE>=dateadd(month,-12,CONVERT(CHAR(10),DATEADD(dd,-DAY(GETDATE())+1,GETDATE()),111)) ";

        if ($cardcode){$sqlquery=$sqlquery."and t0.cardcode='".$cardcode."'" ;}
        $sqlquery=$sqlquery." group by left(CONVERT(varchar(100), t0.docdate, 111),7) order by  left(CONVERT(varchar(100), t0.docdate, 111),7)";
        //echo $sqlquery;die();

        $data = $sqlserver->query($sqlquery);
        //var_dump($data);die();
        $sqlserver->close();

        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '月份');
        $currentSheet->setCellValue('B'.$m, '数量');
        $currentSheet->setCellValue('C'.$m, '销售金额');
        $currentSheet->setCellValue('D'.$m, '销售毛利');
        $currentSheet->setCellValue('E'.$m, '毛利率%');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['month'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,round($value['SUMQTY'],0), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,round($value['SUMPRICE'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,round($value['PROFIT'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,round($value['PROFIT']/$value['SUMPRICE']*100,2),\PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    /**
     * 2017-09-29 报表直接导出Excel: 产品部报表 > 产品部业绩分析（含老积分） -4
     */
    public function classicreports1excel(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);

        // from frontend begin:

        /*if(!empty($_REQUEST['time']))
        { $time=$_REQUEST["time"]; }
        else
        {$time=date("m/d/Y",time()-24*60*60);}

        $FromDate = strstr($time, ' - ',true);
        $ToDate =  substr($time,strpos ($time,' - ')+3,10) ;
        if ($FromDate=="" ){$FromDate=$ToDate=$time;}*/

        $FromDate = $_REQUEST['FromDate'];
        $ToDate = $_REQUEST['ToDate'];

        if(!empty($_REQUEST['check']))
        {
            $check=$_REQUEST["check"];
            $CardCodeS=implode("','",$check);
        }
        else
        {
            $check="All";
            $CardCodeS="All";
        }

        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();

        $CardLimitquery= "and T0.CardCode in ('".$CardCodeS."')" ;
        $sqlquery="select T0.pmanager pmanager, T0.pleader U_LEADER,SUM(Quantity) SUMQTY,SUM(linetotal) SUMPRICE ,sum(isnull([linetotal],0)-isnull([bankcharge],0)-isnull([calclineStockprice],0)-isnull([calcexpressfee],0)-isnull([addexpressfee],0)-isnull([giftcount],0)-isnull([giftaddexpressfee],0)-isnull([calctax],0)) PROFIT from ax_reportx T0
where t0.docdate>='".$FromDate."' and t0.docdate<='".$ToDate."' ";
        if ($CardCodeS!='ALL') {
            $sqlquery=$sqlquery.$CardLimitquery;
        }
        $sqlquery=$sqlquery." group by T0.pmanager, T0.pleader";
        $data = $sqlserver->query($sqlquery);

        $sqlserver->close();

        //Export to Excel:

        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '产品经理');
        $currentSheet->setCellValue('B'.$m, '产品组');
        $currentSheet->setCellValue('C'.$m, '数量');
        $currentSheet->setCellValue('D'.$m, '销售金额');
        $currentSheet->setCellValue('E'.$m, '销售毛利');
        $currentSheet->setCellValue('F'.$m, '毛利率%');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$value['U_LEADER'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,round($value['SUMQTY'],0), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,round($value['SUMPRICE'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,round($value['PROFIT'],2),\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,round($value['PROFIT']/$value['SUMPRICE']*100,2),\PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    /**
     * 2017-09-29 报表直接导出Excel: 产品部报表 > 产品部业绩分析 -5
     */
    public function classicreports1plusexcel(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);

        // from frontend begin:

        /*if(!empty($_REQUEST['time']))
        { $time=$_REQUEST["time"]; }
        else
        {$time=date("m/d/Y",time()-24*60*60);}

        $FromDate = strstr($time, ' - ',true);
        $ToDate =  substr($time,strpos ($time,' - ')+3,10) ;
        if ($FromDate=="" ){$FromDate=$ToDate=$time;}*/

        $FromDate = $_REQUEST['FromDate'];
        $ToDate = $_REQUEST['ToDate'];

        if(!empty($_REQUEST['check']))
        {
            $check=$_REQUEST["check"];
            $CardCodeS=implode("','",$check);
        }
        else
        {
            $check="All";
            $CardCodeS="All";
        }

        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();
        $sqlserver->connent();

        $CardLimitquery= "and T0.CardCode in ('".$CardCodeS."')" ;
        $sqlquery="select T0.pmanager pmanager, gt.dbo.AX_pmanager_info(T0.pmanager,'pleader') U_LEADER,SUM(Quantity) SUMQTY,SUM(linetotal) SUMPRICE ,sum(isnull([linetotal],0)-isnull([bankcharge],0)-isnull([calclineStockprice],0)-isnull([calcexpressfee],0)-isnull([addexpressfee],0)-isnull([giftcount],0)-isnull([giftaddexpressfee],0)-isnull([calctax],0)) PROFIT from ax_reportx2products T0
where t0.docdate>='".$FromDate."' and t0.docdate<='".$ToDate."' ";
        if ($CardCodeS!='ALL')
        {$sqlquery=$sqlquery.$CardLimitquery;}
        $sqlquery=$sqlquery." group by T0.pmanager";
        //echo $sqlquery;
        $data = $sqlserver->query($sqlquery);
        $sqlserver->close();
        //var_dump($summerData);

        /*$sqlquery = "select   pmanager,
SUM(Quantity) SUMQTY,
SUM(linetotal) SUMPRICE ,
sum(profit) PROFIT from  profit_report  where docdate>='".$FromDate."' and docdate<='".$ToDate."' and CardCode in ('".$CardCodeS."') group by pmanager";*/

        //echo $CardCodeS;die();

        /*import("@.ORG.Util.PermissionDatabase");
        $permissionDatabase = PermissionDatabase::getInstance();
        $model = $permissionDatabase->getModel();

        $data = $model->db(1)->query($sqlquery);*/
        //var_dump($data);die();

        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '产品经理');
        $currentSheet->setCellValue('B'.$m, '产品组');
        $currentSheet->setCellValue('C'.$m, '数量');
        $currentSheet->setCellValue('D'.$m, '销售金额');
        $currentSheet->setCellValue('E'.$m, '销售毛利');
        $currentSheet->setCellValue('F'.$m, '毛利率%');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$value['U_LEADER'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,round($value['SUMQTY'],0), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,round($value['SUMPRICE'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,round($value['PROFIT'],2),\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,round($value['PROFIT']/$value['SUMPRICE']*100,2),\PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    /**
     * 2017-09-29 报表直接导出Excel: 共享报表 > 品牌分类平台分析表 -6
     */
    public function brandrankexcel(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);

        $Cardcodes = $_POST['Cardcode'];
        $CardcodeSqlFormate="'".implode("','",$Cardcodes)."'";
        $FromDate = $_POST['FromDate'];
        $ToDate = $_POST['ToDate'];
        $ViewOption = $_POST['ViewOption'];
        $GroupLevel = $_POST['GroupLevel'];
        $OrderBy = $_POST['OrderBy'];
        if ($GroupLevel=='Catlog0') {$GroupLevelFormate="";}
        if ($GroupLevel=='Catlog1') {$GroupLevelFormate="u_category1";}
        if ($GroupLevel=='Catlog2') {$GroupLevelFormate="u_category1+'->>'+u_category2";}
        if ($GroupLevel=='Catlog3') {$GroupLevelFormate="u_category1+'->>'+u_category2+'->>'+u_category3";}
        $OrderBy = $_POST['OrderBy'];
        if ($OrderBy=='Sumgty') {$OrderByFormate="Sum(Quantity)";}
        if ($OrderBy=='Sumprice') {$OrderByFormate="sum(linetotal)";}
        if ($OrderBy=='Sumprofit') {$OrderByFormate="(sum(linetotal)-sum(LineStockPrice)-sum(isnull(bankcharge,0))-sum(CASE when TureExpCost=0 then EstimateExpCost else ISNULL(TureExpCost,0) end)-sum(giftcount)-sum(giftaddexpressfee)-sum(4.719008054))";}

        if ( $ViewOption=='Total'&$GroupLevel!='Catlog0') {
            $sqlquery="select  t1.u_brand,".$GroupLevelFormate." catelog,Sum(Quantity) sumqty,null CardCode,
(sum(linetotal)-sum(LineStockPrice)-sum(isnull(bankcharge,0))-sum(CASE when TureExpCost=0 then EstimateExpCost else ISNULL(TureExpCost,0) end)-sum(giftcount)-sum(giftaddexpressfee)-sum(4.719008054)) profit,
sum(linetotal) sumprice   from ax_reportx T0 left join [ti_z0050x] T1 on T0.ItemCode=t1.u_ItemCode where FreeTxt in (select T2.U_OrderId from [@TI_Z0081] T2 left join [@ti_z0080] T3 on T2.DocEntry=T3.DocEntry WHERE  T3.U_DocDate>='".$FromDate."' and T3.U_DocDate<='".$ToDate."' and T3.U_CardCode in (".$CardcodeSqlFormate."))  GROUP by t1.u_brand,".$GroupLevelFormate." order by ".$OrderByFormate." desc";}
        if ( $ViewOption=='Total'&$GroupLevel=='Catlog0') {
            $sqlquery="select  t1.u_brand, Sum(Quantity) sumqty,null catelog,null CardCode,
(sum(linetotal)-sum(LineStockPrice)-sum(isnull(bankcharge,0))-sum(CASE when TureExpCost=0 then EstimateExpCost else ISNULL(TureExpCost,0) end)-sum(giftcount)-sum(giftaddexpressfee)-sum(3.719008054)) profit,
sum(linetotal) sumprice   from ax_reportx T0 left join [ti_z0050x] T1 on T0.ItemCode=t1.u_ItemCode where FreeTxt in (select T2.U_OrderId from [@TI_Z0081] T2 left join [@ti_z0080] T3 on T2.DocEntry=T3.DocEntry WHERE  T3.U_DocDate>='".$FromDate."' and T3.U_DocDate<='".$ToDate."' and T3.U_CardCode in (".$CardcodeSqlFormate."))   GROUP by t1.u_brand  order by  Sum(Quantity) desc";}
        if ( $ViewOption=='Everyone'&$GroupLevel=='Catlog0') {
            $sqlquery="select  t1.u_brand,gt.dbo.BPcontact(T0.CardCode,'CardName') CardName,T0.CardCode,Sum(Quantity) sumqty,null catelog,
(sum(linetotal)-sum(LineStockPrice)-sum(isnull(bankcharge,0))-sum(CASE when TureExpCost=0 then EstimateExpCost else ISNULL(TureExpCost,0) end)-sum(giftcount)-sum(giftaddexpressfee)-sum(3.719008054)) profit,
sum(linetotal) sumprice   from ax_reportx T0 left join [ti_z0050x] T1 on T0.ItemCode=t1.u_ItemCode where FreeTxt in (select T2.U_OrderId from [@TI_Z0081] T2 left join [@ti_z0080] T3 on T2.DocEntry=T3.DocEntry WHERE  T3.U_DocDate>='".$FromDate."' and T3.U_DocDate<='".$ToDate."' and T3.U_CardCode in (".$CardcodeSqlFormate.")) 
  GROUP by t1.u_brand,T0.CardCode order by  cardname,Sum(Quantity)desc";}
        if ( $ViewOption=='Everyone'&$GroupLevel!='Catlog0') {
            $sqlquery="select  t1.u_brand,gt.dbo.BPcontact(T0.CardCode,'CardName') CardName,T0.CardCode,".$GroupLevelFormate." catelog,Sum(Quantity) sumqty,
(sum(linetotal)-sum(LineStockPrice)-sum(isnull(bankcharge,0))-sum(CASE when TureExpCost=0 then EstimateExpCost else ISNULL(TureExpCost,0) end)-sum(giftcount)-sum(giftaddexpressfee)-sum(3.719008054)) profit,
sum(linetotal) sumprice   from ax_reportx T0 left join [ti_z0050x] T1 on T0.ItemCode=t1.u_ItemCode where FreeTxt in (select T2.U_OrderId from [@TI_Z0081] T2 left join [@ti_z0080] T3 on T2.DocEntry=T3.DocEntry WHERE  T3.U_DocDate>='".$FromDate."' and T3.U_DocDate<='".$ToDate."' and T3.U_CardCode in (".$CardcodeSqlFormate.")) 
  GROUP by t1.u_brand,T0.CardCode,".$GroupLevelFormate." order by  ".$GroupLevelFormate.",cardname,Sum(Quantity)desc";}

        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();
        $sqlserver->connent();
        $data = $sqlserver->query($sqlquery);
        $sqlserver->close();

        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '品牌');
        $currentSheet->setCellValue('B'.$m, '平台');
        $currentSheet->setCellValue('C'.$m, '分类');
        $currentSheet->setCellValue('D'.$m, '销售数量');
        $currentSheet->setCellValue('E'.$m, '销售金额');

        $currentSheet->setCellValue('F'.$m, '毛利');
        $currentSheet->setCellValue('G'.$m, '客单价');
        $currentSheet->setCellValue('H'.$m, '毛利率%');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['u_brand'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$value['CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$value['catelog'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,round($value['sumqty'],0), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,round($value['sumprice'],2),\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,round($value['profit'],2),\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,round($value['sumprice']/$value['sumqty'],2),\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,round($value['profit']/$value['sumprice']*100,4),\PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    /**
     * 2017-09-29 报表直接导出Excel: 共享报表 > 品牌分析 -7
     */
    public function classicreportx2excel(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);

        if(!empty($_REQUEST['FromDate'])){ $FromDate=$_REQUEST["FromDate"]; }
        $ToDate=date("m/d/Y", strtotime("+1 months", strtotime(date("m/",time())."01".date("/Y",time()))));
        if(!empty($_REQUEST['ToDate'])){ $ToDate=$_REQUEST["ToDate"]; }
        $selectdeptidden='ALL';
        if(!empty($_REQUEST['selectdeptidden'])){ $selectdeptidden=$_REQUEST["selectdeptidden"]; }

        if ($selectdeptidden!='ALL') {$namequery= "select dbo.BPcontact('".str_replace(",", "','CardName')+'#'+dbo.BPcontact('", $selectdeptidden)."','CardName') CardName";}
        if ($selectdeptidden!='ALL') {$CardLimitquery= "and T0.CardCode in ('".str_replace(",", "','",$selectdeptidden)."')" ;}

        $sqlquery="select T1.U_BRAND,SUM(Quantity) SUMQTY,SUM(linetotal) SUMPRICE ,sum([linetotal]-[bankcharge]-[calclineStockprice]-[calcexpressfee]-[addexpressfee]-[giftcount]-[giftaddexpressfee]-[calctax]) PROFIT,
rank () over (order by SUM(Quantity) DESC ) RANKQTY,
RANK () Over (order by SUM(linetotal) desc) RANKPRICE ,
rank () over (order by sum([linetotal]-[bankcharge]-[calclineStockprice]-[calcexpressfee]-[addexpressfee]-[giftcount]-[giftaddexpressfee]-[calctax]) desc) RANKPROFIT 
 from ax_reportx T0 left join  OITM t1 on T0.itemcode=t1.itemcode 
where t0.docdate>='".$FromDate."' and t0.docdate<='".$ToDate."' ";
        if ($selectdeptidden!='ALL') {$sqlquery=$sqlquery.$CardLimitquery;}
        $sqlquery=$sqlquery." group by T1.U_Brand";

        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();
        $sqlserver->connent();
        $data = $sqlserver->query($sqlquery);

        $sqlserver->close();

        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '品牌');
        $currentSheet->setCellValue('B'.$m, '销售数量');
        $currentSheet->setCellValue('C'.$m, '销售金额');
        $currentSheet->setCellValue('D'.$m, '销售毛利');
        $currentSheet->setCellValue('E'.$m, '数量排名');
        $currentSheet->setCellValue('F'.$m, '金额排名');
        $currentSheet->setCellValue('G'.$m, '毛利排名');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['U_BRAND'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,round($value['SUMQTY'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,round($value['SUMPRICE'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,round($value['PROFIT'],2), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$value['RANKQTY'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$value['RANKPRICE'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$value['RANKPROFIT'],\PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    /**
     * 2017-09-29 报表直接导出Excel: 共享报表 > 品牌排名(表头不定) -8
     */
    public function topbrandexcel(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);

        //Export to Excel:
        $data = array(
            array('item_name'=>'item name a'),
            array('item_name'=>'item name b')
        );

        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '品牌');
        $currentSheet->setCellValue('B'.$m, '月份');
        $currentSheet->setCellValue('C'.$m, '月份');
        $currentSheet->setCellValue('D'.$m, '月份');
        $currentSheet->setCellValue('E'.$m, '月份');

        $currentSheet->setCellValue('F'.$m, '月份');
        $currentSheet->setCellValue('G'.$m, '月份');
        $currentSheet->setCellValue('H'.$m, '月份');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['item_name'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$value['item_name'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$value['item_name'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$value['item_name'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$value['item_name'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$value['item_name'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$value['item_name'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$value['item_name'],\PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    public function getCList()
    {
        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();

        $sqlquery="select cardcode U_BPcode,ShortName Name from dbo.[AX_ChannelList]";
        $cList = $sqlserver->query($sqlquery);
        $sqlserver->close();

        return $cList;

        //echo json_encode($cList);
        //exit;
    }

    public function getCListBaseUser(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);

        $allPTList = $this->getCList();

        if($userInfo['dept'] != "eccbc87e4b5ce2fe28308fd9f2a7baf3") {
            $theList = array();
            $specialCList = explode(",", $userInfo['Customer_Code']);
            foreach ($specialCList as $key => $value) {
                $trueC = str_replace("'", "", $value);
                foreach ($allPTList as $inKey => $inValue) {
                    if ($inValue['U_BPcode'] == $trueC) {
                        $theList[] = $inValue;
                    }
                }
            }
        }
        else{
            $theList = $allPTList;
        }

        echo json_encode($theList);
        exit;

    }

    public function getPTList()
    {
        $sqlquery="select CardCode U_BPcode,ShortName Name, ChannelType, ChannelTypeCode from AX_ChannelList";
        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();
        $sqlserver->connent();
        $allList = $sqlserver->query($sqlquery);
        $newList = array();
        foreach ($allList as $key => $pt)
        {
            $newList[$pt['U_BPcode']] = $pt['Name'].'|'.$pt['ChannelType'].'|'.$pt['ChannelTypeCode'];
        }
        $sqlserver->close();
        return $newList;
    }

    public function profitreportx20170930(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $this->assign("userInfo",$userInfo);

        $newList = $this->getPTList();
        if($userInfo['dept'] != "eccbc87e4b5ce2fe28308fd9f2a7baf3") {
            $specialCList = explode(",", $userInfo['Customer_Code']);
            foreach ($specialCList as $key => $value) {
                $trueC = str_replace("'", "", $value);
                $options[] = array($trueC, $newList[$trueC]);
            }
        }else{
            foreach ($newList as $key => $value)
            {
                $options[] = array($key,$value);
            }
        }
        $this->assign("options",$options);

        $this->display();
    }

    public function classic320170930(){
        $this->display();
    }

    public function bptrendx20170930(){

        /*
        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $this->assign("userInfo",$userInfo);

        $newList = $this->getPTList();
        if($userInfo['dept'] != "eccbc87e4b5ce2fe28308fd9f2a7baf3") {
            $specialCList = explode(",", $userInfo['Customer_Code']);
            foreach ($specialCList as $key => $value) {
                $trueC = str_replace("'", "", $value);
                $options[] = array($trueC, $newList[$trueC]);
            }
        }else{
            foreach ($newList as $key => $value)
            {
                $options[] = array($key,$value);
            }
        }
        $this->assign("options",$options);
        */

        $this->display();

    }

    public function classic120170930(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $this->assign("userInfo",$userInfo);

        if(!empty($_REQUEST['time']))
        { $time=$_REQUEST["time"]; }
        else
        {$time=date("m/d/Y",time()-24*60*60);}

        $FromDate = strstr($time, ' - ',true);
        $ToDate =  substr($time,strpos ($time,' - ')+3,10) ;
        if ($FromDate=="" ){$FromDate=$ToDate=$time;}

        if(!empty($_REQUEST['check']))
        {
            $check=$_REQUEST["check"];
            $CardCodeS=implode("','",$check);
        }
        else
        {
            $check="All";
            $CardCodeS="All";
        }

        if ($CardCodeS!='ALL')
        {
            $options1=array();
        }

        $sqlquery="SELECT [CardCode],[ShortName],[ChannelType],[CManager],[CLearder] FROM [dbo].[AX_ChannelList] order by ChannelType";

        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();
        $oldOptions = $sqlserver->query($sqlquery);

        //var_dump($oldOptions);

        if($userInfo['dept'] != "eccbc87e4b5ce2fe28308fd9f2a7baf3" && $userInfo['dept'] != "c4ca4238a0b923820dcc509a6f75849b") {

            $specialCList = explode(",", $userInfo['Customer_Code']);
            $options = array();
            foreach ($specialCList as $key => $value) {
                $trueC = str_replace("'", "", $value);
                //create new options
                foreach ($oldOptions as $inKey => $inValue) {
                    if ($inValue['CardCode'] == $trueC) {
                        $options[] = $inValue;
                    }
                }
            }

        }
        else{
            $options = $oldOptions;
        }

        $CardLimitquery= "and T0.CardCode in ('".$CardCodeS."')" ;
        $sqlquery="select T0.pmanager pmanager, T0.pleader U_LEADER,SUM(Quantity) SUMQTY,SUM(linetotal) SUMPRICE ,sum(isnull([linetotal],0)-isnull([bankcharge],0)-isnull([calclineStockprice],0)-isnull([calcexpressfee],0)-isnull([addexpressfee],0)-isnull([giftcount],0)-isnull([giftaddexpressfee],0)-isnull([calctax],0)) PROFIT from ax_reportx T0
where t0.docdate>='".$FromDate."' and t0.docdate<='".$ToDate."' ";
        if ($CardCodeS!='ALL') {
            $sqlquery=$sqlquery.$CardLimitquery;
        }
        $sqlquery=$sqlquery." group by T0.pmanager, T0.pleader";
        //echo $sqlquery;
        $summerData = $sqlserver->query($sqlquery);
        //var_dump($summerData);
        $sqlserver->close();

        $this->assign("summerData",$summerData);
        $this->assign("options",$options);
        $this->assign("time",$time);
        $this->display();

    }

    public function productkpi20170930()
    {

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $this->assign("userInfo",$userInfo);

        if(!empty($_REQUEST['time']))
        { $time=$_REQUEST["time"]; }
        else
        {$time=date("m/d/Y",time()-24*60*60);}

        $FromDate = strstr($time, ' - ',true);
        $ToDate =  substr($time,strpos ($time,' - ')+3,10) ;
        if ($FromDate=="" ){$FromDate=$ToDate=$time;}

        if(!empty($_REQUEST['check']))
        {
            $check=$_REQUEST["check"];
            $CardCodeS=implode("','",$check);
        }
        else
        {
            $check="All";
            $CardCodeS="All";
        }

        if ($CardCodeS!='ALL')
        {
            $options1=array();
        }

        $sqlquery="SELECT [CardCode],[ShortName],[ChannelType],[CManager],[CLearder] FROM [dbo].[AX_ChannelList] order by ChannelType";

        import("@.ORG.Util.Sqlserver");
        $sqlserver  = new Sqlserver();
        $oldOptions = $sqlserver->query($sqlquery);

        //var_dump($oldOptions);

        if($userInfo['dept'] != "eccbc87e4b5ce2fe28308fd9f2a7baf3" && $userInfo['dept'] != "c4ca4238a0b923820dcc509a6f75849b") {

            $specialCList = explode(",", $userInfo['Customer_Code']);
            $options = array();
            foreach ($specialCList as $key => $value) {
                $trueC = str_replace("'", "", $value);
                //create new options
                foreach ($oldOptions as $inKey => $inValue) {
                    if ($inValue['CardCode'] == $trueC) {
                        $options[] = $inValue;
                    }
                }
            }

        }
        else{
            $options = $oldOptions;
        }

        //var_dump($options);
        //die();

        //查询结果：
        /*$CardLimitquery= "and T0.CardCode in ('".$CardCodeS."')" ;
        $sqlquery="select T0.pmanager pmanager, gt.dbo.AX_pmanager_info(T0.pmanager,'pleader') U_LEADER,SUM(Quantity) SUMQTY,SUM(linetotal) SUMPRICE ,sum(isnull([linetotal],0)-isnull([bankcharge],0)-isnull([calclineStockprice],0)-isnull([calcexpressfee],0)-isnull([addexpressfee],0)-isnull([giftcount],0)-isnull([giftaddexpressfee],0)-isnull([calctax],0)) PROFIT from ax_reportx2products T0
where t0.docdate>='".$FromDate."' and t0.docdate<='".$ToDate."' ";
        if ($CardCodeS!='ALL')
        {$sqlquery=$sqlquery.$CardLimitquery;}
        $sqlquery=$sqlquery." group by T0.pmanager";
        $summerData = $sqlserver->query($sqlquery);*/

        $sqlquery = "select   pmanager, 
SUM(Quantity) SUMQTY,
SUM(linetotal) SUMPRICE ,
sum(profit) PROFIT from  profit_report  where docdate>='".$FromDate."' and docdate<='".$ToDate."' and CardCode in ('".$CardCodeS."') group by pmanager";

        import("@.ORG.Util.PermissionDatabase");
        $permissionDatabase = PermissionDatabase::getInstance();
        $model = $permissionDatabase->getModel();

        $summerData = $model->db(1)->query($sqlquery);
        //var_dump($data);die();

        $this->assign("summerData",$summerData);
        $this->assign("options",$options);
        $this->assign("time",$time);
        $this->display();

    }

    public function brandrank20170930(){

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $this->assign("userInfo",$userInfo);

        $newList = $this->getPTList();
        if($userInfo['dept'] != "eccbc87e4b5ce2fe28308fd9f2a7baf3" && $userInfo['dept'] != "c4ca4238a0b923820dcc509a6f75849b") {
            $specialCList = explode(",", $userInfo['Customer_Code']);
            foreach ($specialCList as $key => $value) {
                $trueC = str_replace("'", "", $value);
                $options[] = array($trueC, $newList[$trueC]);
            }
        }else{
            foreach ($newList as $key => $value)
            {
                $options[] = array($key,$value);
            }
        }
        $this->assign("options",$options);

        $this->display();
    }

    public function classic220170930()
    {
        $this->display();
    }

    public function stickstockreport20171018()
    {
        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $pmList = $userInfo['pmlist'];
        $ptList = $userInfo['Customer_Code'];
        $userDept = $userInfo['dept'];

        $this->assign("userInfo",$userInfo);

        if(!empty($_REQUEST['Viewoption'])){ $Viewoption=$_REQUEST["Viewoption"]; } else {$Viewoption="day";}
        if(!empty($_REQUEST['StartDate'])){ $StartDate=$_REQUEST["StartDate"]; } else {$StartDate=date("m/d/Y", strtotime("1 days ago"));}

        //$sqlquery = "EXEC gt.dbo.[AX_StickReport] @interval='".$Viewoption."',@StartDate='".$StartDate."'";
        $sqlquery = "EXEC gt.dbo.[AX_StickReport] 
        @StartDate = '".$StartDate."'
  ,@interval = '".$Viewoption."'
  ,@pmanagerstring = '".$pmList."'
  ,@cardcodeString = '".$ptList."'
  ,@department = '".$userDept."'
";
        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        $result = $sqlserver->query($sqlquery);
        //echo $sqlquery;

        $checkdate='';
        $StickStock='';
        $NoneStickStock='';
        $tmpArray = array_reverse($result);
        foreach ($tmpArray as $key => $item)
        {
            $checkdate .= '\''.substr($item['checkdate'],4,2).'/'.substr($item['checkdate'],-2).'\',';
            $StickStock .= round(($item['StickStock'] / 10000), 2) . ",";
            $NoneStickStock .= round(($item['NoneStickStock'] / 10000), 2) . ",";
        }

        $controllerURL = "index.php?m=&c=report&a=stickstockreport20171018";

        $this->assign("checkdate",$checkdate);
        $this->assign("StartDate",$StartDate);
        $this->assign("controllerURL",$controllerURL);
        $this->assign("checkdate",$checkdate);
        $this->assign("StickStock",$StickStock);
        $this->assign("NoneStickStock",$NoneStickStock);

        $this->display();
    }

    /**
     * 新报表启动 - Based Excel File: day20171017
     * @date 2017-10-19
     */

    public function getPPList()
    {
        import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $options = $sapBrandImpl->getBrandListOptions();
        $ppJson = json_encode($options);
        echo $ppJson;
        exit;
    }

    function listProductManager(){
        $productManagerModel = M("ProductManagerForReport");
        $result = $productManagerModel->order("captain_no desc")->select();
        echo json_encode($result);
    }

    function updateProductManager(){
        $productManagerModel = M("ProductManagerForReport");
        $data = json_decode(I('data'), 1);
        foreach ($data as $key => $rowData){
            if ($rowData['_state'] == "removed"){
                $productManagerModel->where("id = {$rowData['id']}")->delete();
            }
            if ($rowData['_state'] == "added"){
                $productManagerModel->add($rowData);
            }
            if ($rowData['_state'] == "modified"){
                $productManagerModel->save($rowData);
            }
        }
    }

    public function getPMList()
    {
        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');

        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);

        import("@.ORG.Util.ProductManager");
        $productManagerImpl = ProductManager::getInstance();

        $pmlist = '';

        if($userInfo['dept'] == "eccbc87e4b5ce2fe28308fd9f2a7baf3" || $userInfo['dept'] == "c81e728d9d4c2f636f067f89cc14862c" )
        {
            $pmlist = $productManagerImpl->getAllProductManagerOptions();
        }else{
            $pmlist = $productManagerImpl->getAllListOptions($emp_no);
        }

        $pmJson = json_encode($pmlist);
        echo $pmJson;
        exit;
    }

    public function getSSList()
    {
        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');

        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);

        import("@.ORG.Util.SalesSupervisor");
        $salesSupervisorImpl = SalesSupervisor::getInstance();

        $sslist = '';

        if($userInfo['dept'] == "eccbc87e4b5ce2fe28308fd9f2a7baf3" || $userInfo['dept'] == "c4ca4238a0b923820dcc509a6f75849b" )
        {
            //die();
            $sslist = $salesSupervisorImpl->getAllSalesSupervisorListOptions();
        }else{
            $sslist = $salesSupervisorImpl->getAllListOptions($emp_no);
        }

        $ssJson = json_encode($sslist);
        echo $ssJson;
        exit;
    }

    public function pmMemberMerge($pmList)
    {
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();

        $pmArray = explode(',',$pmList);
        $newPMList = "";
        $tmpList = "";

        foreach ($pmArray as $key => $pm) {
            $userInfo = $userRole->getPMListbyEmpNo($pm);
            $tmpList .= $userInfo['pmlist']. ',';
        }

        $newPMList = substr($tmpList,0,strlen($tmpList)-1);

        return $newPMList;
    }

    public function ssMemberMerge($ssList)
    {
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();

        $ssArray = explode(',',$ssList);
        $newSSList = "";
        $tmpList = "";

        foreach ($ssArray as $key => $ss) {
            $userInfo = $userRole->getSSListbyEmpNo($ss);
            $tmpList .= $userInfo['sslist']. ',';
        }

        $newSSList = substr($tmpList,0,strlen($tmpList)-1);

        return $newSSList;
    }

    public function getPingTaiList()
    {
        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);

        $newList = $this->getPTList();
        if($userInfo['dept'] != "eccbc87e4b5ce2fe28308fd9f2a7baf3" && $userInfo['dept'] != "c4ca4238a0b923820dcc509a6f75849b") {
            $specialCList = explode(",", $userInfo['Customer_Code']);
            foreach ($specialCList as $key => $value) {
                $trueC = str_replace("'", "", $value);
                //$options[] = array('id'=>'\''.$trueC.'\'','text'=>$newList[$trueC]);
                $options[] = array('id'=>$trueC,'text'=>$newList[$trueC]);
            }
        }else{
            foreach ($newList as $key => $value)
            {
                //$options[] = array('id'=>'\''.$key.'\'','text'=>$value);
                $options[] = array('id'=>$key,'text'=>$value);
            }
        }
        $ptJson = json_encode($options);
        echo $ptJson;
        exit;
    }
    public function singleQuotationList($list)
    {
        $tmpList = "";
        $listArray = explode(',',$list);
        foreach ($listArray as $key => $value)
        {
            //$tmpList .= '\''.$value.'\',';
            $tmpList .= $value.',';
        }
        $newList = substr($tmpList,0,strlen($tmpList)-1);

        return $newList;
    }

    public function maoliandxiaoshou20171018()
    {
        $this->display();
    }

    public function maoliandxiaoshou20171018data()
    {
        // from html
        $startDate = I('startDate');
        $endDate = I('endDate');
        $itemName = I('itemName');
        $itemName=mb_convert_encoding($itemName,'gbk','utf-8');
        $ptList = $this->singleQuotationList(I('ptlist'));//I('ptlist');
        $queryType = I('queryType');

        $pageIndex = I('pageIndex');
        $pageSize = I('pageSize')?:50000;
        $where='1 = 1 ';

        // from userRole
        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $pmList = $userInfo['pmlist'];
        $userDept = $userInfo['dept'];

        $sqlquery = "EXEC [dbo].[A_ProfitAndSaleQuery]
		@pmanagerstring = '".$pmList."',
		@FromDate = '".$startDate."',
		@ToDate = '".$endDate."',
		@cardcodeString='".$ptList."',
		@namestring='".$itemName."',
		@department = '".$userDept."',
		@isgrouped='".$queryType."'";
        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        $result = $sqlserver->query($sqlquery);

        //echo $sqlquery;die();

        //$total =$model->where($where)->order("b_id desc")->count();
        //$res = $model->where($where)->order("b_id desc")->limit($pageIndex*$pageSize,$pageSize)->select();
        //echo json_encode(array('total'=>$total,'data'=>$res));exit;
        /*$result = array(array('itemCode'=>$startDate,'itemName'=>$endDate,'sumQty'=>$itemName,'sumProfit'=>$ptList,'profitRate'=>$queryType,'avgPrice'=>$pmList,'sumLineTotal'=>$userDept),
            array('itemCode'=>$startDate,'itemName'=>$endDate,'sumQty'=>$itemName,'sumProfit'=>$ptList,'profitRate'=>$queryType,'avgPrice'=>$pmList,'sumLineTotal'=>$userDept));*/
        echo json_encode(array('total'=>1,'data'=>$result));exit;
    }

    public function maoliandxiaoshou20171018excel()
    {
        // from html
        $startDate = I('startDate');
        $endDate = I('endDate');
        $itemName = I('itemName');
        $itemName=mb_convert_encoding($itemName,'gbk','utf-8');
        $ptList = $this->singleQuotationList(I('ptlist'));//I('ptlist');
        $queryType = I('queryType');

        $pageIndex = I('pageIndex');
        $pageSize = I('pageSize')?:50000;
        $where='1 = 1 ';

        // from userRole
        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $pmList = $userInfo['pmlist'];
        $userDept = $userInfo['dept'];

        $sqlquery = "EXEC [dbo].[A_ProfitAndSaleQuery]
		@pmanagerstring = '".$pmList."',
		@FromDate = '".$startDate."',
		@ToDate = '".$endDate."',
		@cardcodeString='".$ptList."',
		@namestring='".$itemName."',
		@department = '".$userDept."',
		@isgrouped='".$queryType."'";
        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        $data = $sqlserver->query($sqlquery);

        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '物料号');
        $currentSheet->setCellValue('B'.$m, '物料名');
        $currentSheet->setCellValue('C'.$m, '数量');
        $currentSheet->setCellValue('D'.$m, '毛利');
        $currentSheet->setCellValue('E'.$m, '毛利率%');

        $currentSheet->setCellValue('F'.$m, '销售单价');
        $currentSheet->setCellValue('G'.$m, '销售金额');
        $currentSheet->setCellValue('H'.$m, '采购单价');
        $currentSheet->setCellValue('I'.$m, '库存价值');
        $currentSheet->setCellValue('J'.$m, '银行费用');

        $currentSheet->setCellValue('K'.$m, '平均费率');
        $currentSheet->setCellValue('L'.$m, '快递费用');
        $currentSheet->setCellValue('M'.$m, '平均快递费用');
        $currentSheet->setCellValue('N'.$m, '赠品件数');
        $currentSheet->setCellValue('O'.$m, '赠品成本');

        $currentSheet->setCellValue('P'.$m, '赠品运费');
        $currentSheet->setCellValue('Q'.$m, '物流附加');
        $currentSheet->setCellValue('R'.$m, '税');
        $currentSheet->setCellValue('S'.$m, '平台');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['itemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$value['itemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$value['sumQty'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$value['sumProfit'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$value['profitRate'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$value['avgPrice'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$value['sumLineTotal'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$value['avgStockprice'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$value['sumLineStockPrice'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$value['sumBankCharge'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$value['avgBankCharge'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$value['sumCalcExpressFee'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$value['avgCalcExpressFee'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$value['sumGiftCount'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$value['sumGiftCost'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$value['sumGiftAddExpressFee'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$value['sumAddExpressFee'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$value['sumCalcTax'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$value['shortname'], \PHPExcel_Cell_DataType::TYPE_STRING);
        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public function zhixiaokucunmingxi20171018()
    {
        // from html
        $checkdate = I('checkdate');

        if (!empty($checkdate)) {}
        else {$checkdate=date("m/d/Y", strtotime("1 days ago")); }
        $this->assign("checkdate",$checkdate);

        // from userRole
        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $plaitPMList = $userInfo['pmlist'];
        $ptList = $userInfo['Customer_Code'];
        $userDept = $userInfo['dept'];

        //SQL Server
        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();

        //产品经理清单(仅产品部，应用权限)
        $pmSqlquery="select distinct(U_Pmanager) Pmanager from (
select U_PManager  from AX_StockStickValue Where checkdate='".$checkdate."' and u_is3c='3C' and stickdate>=7
union all
select  U_PManager from AX_StockStickValue Where checkdate='".$checkdate."' and u_is3c!='3C' and stickdate>=30 ) TX
";
        $pmList = $sqlserver->query($pmSqlquery);
        //$pmList = '';

        $newPMList = array();
        if($userDept == "eccbc87e4b5ce2fe28308fd9f2a7baf3")
        {
            // 最高权限全可见
            $newPMList = $pmList;
        }
        else{
            if(empty($plaitPMList))
            {
                // 市场部不可见
                die();
            }else{
                // 如果是产品部必过滤
                $pmArray = explode(',',$userInfo['pmlist']);
                $newPMList[] = array("Pmanager"=>'');
                foreach ($pmList as $keyPM => $pmItem)
                {
                    if(in_array($pmItem['Pmanager'],$pmArray))
                    {
                        $newPMList[] = $pmItem;
                    }
                }
            }
        }

        //结果数据(仅产品部，应用权限)
        /*$sqlquery="select ItemCode,stockqty,stockvalue,lastmovedate,ItemName,U_PManager,U_Is3C,U_Brand,stickdate from AX_StockStickValue Where checkdate='".$checkdate."' and u_is3c='3C' and stickdate>=7
union all
select ItemCode,stockqty,stockvalue,lastmovedate,ItemName,U_PManager,U_Is3C,U_Brand,stickdate from AX_StockStickValue Where checkdate='".$checkdate."' and u_is3c!='3C' and stickdate>=30
";*/
        $sqlquery = "EXEC [dbo].[A_StickStockDetail]
		@StartDate = '".$checkdate."',
		@pmanagerstring='".$plaitPMList."',
		@department = '".$userDept."'";
        //echo $sqlquery;die();
        $resultData = $sqlserver->query($sqlquery);

        //$resultData = '';
        //var_dump(count($resultData));die();

        $this->assign("pmList",$newPMList);
        $this->assign("resultData",$resultData);
        $this->display();

    }

    public function zhixiaokucunmingxi20171018excel()
    {
        $checkdate = I('checkdate');
        if (!empty($checkdate)) {}
        else {$checkdate=date("m/d/Y", strtotime("1 days ago")); }

        // from userRole
        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        // 注意产品环境上市场部权限表结构还未更新！
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $plaitPMList = $userInfo['pmlist'];
        $ptList = $userInfo['Customer_Code'];
        $userDept = $userInfo['dept'];

        //SQL Server
        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();

        //结果数据(仅产品部，应用权限)
        /*$sqlquery="select ItemCode,stockqty,stockvalue,lastmovedate,ItemName,U_PManager,U_Is3C,U_Brand,stickdate from AX_StockStickValue Where checkdate='".$checkdate."' and u_is3c='3C' and stickdate>=7
union all
select ItemCode,stockqty,stockvalue,lastmovedate,ItemName,U_PManager,U_Is3C,U_Brand,stickdate from AX_StockStickValue Where checkdate='".$checkdate."' and u_is3c!='3C' and stickdate>=30
";*/
        $sqlquery = "EXEC [dbo].[A_StickStockDetail]
		@StartDate = '".$checkdate."',
		@pmanagerstring='".$plaitPMList."',
		@department = '".$userDept."'";

        $data = $sqlserver->query($sqlquery);

        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '经理');
        $currentSheet->setCellValue('B'.$m, '物料号');
        $currentSheet->setCellValue('C'.$m, '物料名');
        $currentSheet->setCellValue('D'.$m, '数量');
        $currentSheet->setCellValue('E'.$m, '库存价值');
        $currentSheet->setCellValue('F'.$m, '最后销售日期');

        $currentSheet->setCellValue('G'.$m, '是否3C');
        $currentSheet->setCellValue('H'.$m, '品牌');
        $currentSheet->setCellValue('I'.$m, '滞销日');
        $currentSheet->setCellValue('J'.$m, '客户经理');

        $cellIndex = 1;
        foreach ($data as $key => $result){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$result['U_PManager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$result['ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$result['ItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,number_format(round($result['stockqty'],2)), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,number_format(round($result['stockvalue'],2)), \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,date('Y/m/d', strtotime($result['lastmovedate'])), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$result['U_Is3C'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$result['U_Brand'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$result['stickdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$result['cmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    public function zhixiaokucunPMLast12Monthes20171018()
    {
        $pm = $_REQUEST['pm'];
        $this->assign('pm',$pm);
        $this->display();
    }

    public function zhixiaokucunPMLast12Monthes20171018data()
    {
        $pm = strtolower($_REQUEST['pm']);

        // from userRole
        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $pmList = $userInfo['pmlist'];
        $userDept = $userInfo['dept'];

        $passTag = 0;
        if($userDept == "eccbc87e4b5ce2fe28308fd9f2a7baf3")
        {
            //最高权限，不检测
            $passTag = 1;
        }elseif ($userDept == "c81e728d9d4c2f636f067f89cc14862c")
        {
            //市场部，不可见
            die();
        }elseif ($userDept == "c4ca4238a0b923820dcc509a6f75849b")
        {
            //产品部，验证权限
            $pmArray = explode(',',$pmList);
            if(in_array($pm,$pmArray))
            {
                $passTag = 1;
            }
        }

        if($passTag) {
            $sqlquery = "EXEC [dbo].[A_StickStockDetailByPmanager]
		@Pmanager = '".$pm."'";
            //import("@.ORG.Util.Sqlserver");
            //$sqlserver  = new Sqlserver();
            import("@.ORG.Util.Sqlserverforreport");
            $sqlserver  = new Sqlserverforreport();
            $result = $sqlserver->query($sqlquery);
            $correctData = array();
            foreach ($result as $key => $item)
            {
                $keyNo = 'm'.$item['row_number'];
                $correctData[$keyNo] = $item['sumvalue'];
            }
            $correctData['name'] = $pm;
            echo json_encode(array('total' => 1, 'data' => $correctData));
        }

        exit;
    }

    public function wuliaojikucun20171018()
    {
        $this->display();
    }

    public function wuliaojikucun20171018data()
    {
        // from html
        $itemName = I('itemName');
        $itemName=mb_convert_encoding($itemName,'gbk','utf-8');
        $ptList = I('ptlist');
        $queryType = I('queryType');

        $pageIndex = I('pageIndex');
        $pageSize = I('pageSize')?:50000;
        $where='1 = 1 ';

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $pmList = $userInfo['pmlist'];
        $userDept = $userInfo['dept'];

        $sqlquery = "EXEC [dbo].[A_Itemreport]
		@itemname = '".$itemName."',
		@pmanagerstring = '".$pmList."',
		@cardcodeString='".$ptList."',
		@department = '".$userDept."',
		@isgrouped='".$queryType."',
		@codeOrname='name'";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        $result = $sqlserver->query($sqlquery);
        //var_dump(count($result));die();

        //$result = array(array('itemCode'=>$itemName,'itemName'=>$ptList,'sumQty'=>$queryType));
        echo json_encode(array('total'=>1,'data'=>$result));exit;
    }

    public function wuliaojikucun20171018excel()
    {
        // from html
        $itemName = I('itemName');
        $itemName=mb_convert_encoding($itemName,'gbk','utf-8');
        $ptList = I('ptlist');
        $queryType = I('queryType');

        $pageIndex = I('pageIndex');
        $pageSize = I('pageSize')?:50000;
        $where='1 = 1 ';

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $pmList = $userInfo['pmlist'];
        $userDept = $userInfo['dept'];

        $sqlquery = "EXEC [dbo].[A_Itemreport]
		@itemname = '".$itemName."',
		@pmanagerstring = '".$pmList."',
		@cardcodeString='".$ptList."',
		@department = '".$userDept."',
		@isgrouped='".$queryType."',
		@codeOrname='name'";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        $data = $sqlserver->query($sqlquery);

        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '物料号');
        $currentSheet->setCellValue('B'.$m, '物料描述');
        $currentSheet->setCellValue('C'.$m, '产品经理');
        $currentSheet->setCellValue('D'.$m, '称重');
        $currentSheet->setCellValue('E'.$m, '称重方式');

        $currentSheet->setCellValue('F'.$m, '采购提前');
        $currentSheet->setCellValue('G'.$m, '包装方式');
        $currentSheet->setCellValue('H'.$m, '品牌');
        $currentSheet->setCellValue('I'.$m, '3C');
        $currentSheet->setCellValue('J'.$m, '存货量');

        $currentSheet->setCellValue('K'.$m, '在库库存');
        $currentSheet->setCellValue('L'.$m, '可用库存');
        $currentSheet->setCellValue('M'.$m, '库存价值');
        $currentSheet->setCellValue('N'.$m, '库存总价值');
        $currentSheet->setCellValue('O'.$m, '总采购量');

        $currentSheet->setCellValue('P'.$m, '累计销量');
        $currentSheet->setCellValue('Q'.$m, '30天内销量');
        $currentSheet->setCellValue('R'.$m, '最后出库时间');
        $currentSheet->setCellValue('S'.$m, '最早入库时间');

        $currentSheet->setCellValue('T'.$m, '滞销天数');
        $currentSheet->setCellValue('U'.$m, '京东价');
        $currentSheet->setCellValue('V'.$m, '天猫价');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$value['ItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$value['u_pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$value['grossweight'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$value['dataway'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$value['LeadTime'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$value['TrnspName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$value['U_Brand'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$value['U_Is3C'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$value['T_OnHand'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$value['stockinWH'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$value['available'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$value['M_AVGPRICE'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$value['stockvalue'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$value['sumpoorderqty'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$value['allsale'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$value['allsalelastmonth'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$value['lastsale'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$value['lastbuy'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('T'.$cellIndex,$value['windowsday'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('U'.$cellIndex,$value['JDprice'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('V'.$cellIndex,$value['TMprice'], \PHPExcel_Cell_DataType::TYPE_STRING);
        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;


    }

    public function pinpailist20171018()
    {
        $this->display();
    }

    public function pinpailist20171018data()
    {
        // from html
        $ppList = I('ppList');
        //$ppName=mb_convert_encoding($ppName,'gbk','utf-8');
        $pmlist = I('pmlist');
        $newPMList = $this->pmMemberMerge($pmlist);

        import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $ppName = $sapBrandImpl->getPPNameByPPList($ppList);


        $sqlquery = "EXEC [dbo].[A_BrandList] 
        @Brandstring='".$ppName."',
        @PmanagerString='".$newPMList."'";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $result = $sqlserver->query($sqlquery);

        //$result = array(array('U_Brand'=>$ppName,'LV1name'=>$newPMList,'LV2name'=>$ppList));
        echo json_encode(array('total'=>1,'data'=>$result));exit;
    }

    public function pinpailist20171018excel()
    {
        // from html
        $ppList = I('ppList');
        //$ppName=mb_convert_encoding($ppName,'gbk','utf-8');
        $pmlist = I('pmlist');
        $newPMList = $this->pmMemberMerge($pmlist);

        import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $ppName = $sapBrandImpl->getPPNameByPPList($ppList);


        $sqlquery = "EXEC [dbo].[A_BrandList] 
        @Brandstring='".$ppName."',
        @PmanagerString='".$newPMList."'";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $data = $sqlserver->query($sqlquery);

        //$data = array(array('itemCode'=>$ppName,'itemName'=>$newPMList,'ppID'=>$ppList));

        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '品牌');
        $currentSheet->setCellValue('B'.$m, '一级分类');
        $currentSheet->setCellValue('C'.$m, '二级分类');
        $currentSheet->setCellValue('D'.$m, '三级分类');
        $currentSheet->setCellValue('E'.$m, '产品经理');
        $currentSheet->setCellValue('F'.$m, 'SKU总数量');
        $currentSheet->setCellValue('G'.$m, '动销SKU/月');

        $cellIndex = 1;
        foreach ($data as $key => $result){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$result['U_Brand'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$result['LV1name'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$result['LV2name'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$result['LV3name'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$result['U_PManager'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$result['itemcodecount'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$result['sale30itemcount'], \PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;


    }

    public function pinpaiskulist20171018()
    {
        $brandName = $_REQUEST['brandName'];
        $this->assign('brandName',$brandName);
        $this->display();
    }

    public function pinpaiskulist20171018data()
    {
        $brandName = I('brandName');
        $brandName=mb_convert_encoding($brandName,'gbk','utf-8');

        /*import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $ppName = $sapBrandImpl->getPPNameByPPList($brandName);*/

        $sqlquery = "EXEC [dbo].[A_ItemListQuery] 
	 @pmanagerstring = '',
	 @cardcodeString = '',
	 @department = '',
	 @isgrouped = '',
	 @querycolumn = 'U_brand',
	 @querystring = '".$brandName."'";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $result = $sqlserver->query($sqlquery);

        //$result = array(array('name'=>$brandName));
        echo json_encode(array('total'=>1,'data'=>$result));exit;
    }

    public function chanpinxingzhi20171018()
    {
        $this->display();
    }

    public function chanpinxingzhi20171018data()
    {
        // from html
        $startDate = trim(I('startDate'));
        $endDate = trim(I('endDate'));
        $pmlist = I('pmlist');
        $newPMList = $this->pmMemberMerge($pmlist);

        $ptlist = I('ptlist');

        $sslist = I('sslist');
        if(!empty($sslist)) {
            $newSSList = $this->ssMemberMerge($sslist);
        }else{
            $newSSList = '';
        }

        $sqlquery = "EXEC [dbo].[A_ProductAnalysis] 
@pmanagerstring = '".$newPMList."',
@cardcodeString = '".$ptlist."',
@FromDate = '".$startDate."',
@ToDate = '".$endDate."',
@brandstring = '',
@cmanagerstring = '".$newSSList."',
@namestring = '',
@department = ''";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $result = $sqlserver->query($sqlquery);

        //$result = array(array('chanpinxingzhi'=>$startDate,'shuliang'=>$endDate,'xiaoshoujinger'=>$newPMList,'xiaoshoumaoli'=>$ptlist,'maolilv'=>$newSSList));
        echo json_encode(array('total'=>1,'data'=>$result));exit;

    }

    public function chanpinxingzhi20171018excel()
    {
        // from html
        $startDate = trim(I('startDate'));
        $endDate = trim(I('endDate'));
        $pmlist = I('pmlist');
        $newPMList = $this->pmMemberMerge($pmlist);

        $ptlist = I('ptlist');

        $sslist = I('sslist');
        if(!empty($sslist)) {
            $newSSList = $this->ssMemberMerge($sslist);
        }else{
            $newSSList = '';
        }

        $sqlquery = "EXEC [dbo].[A_ProductAnalysis] 
@pmanagerstring = '".$newPMList."',
@cardcodeString = '".$ptlist."',
@FromDate = '".$startDate."',
@ToDate = '".$endDate."',
@brandstring = '',
@cmanagerstring = '".$newSSList."',
@namestring = '',
@department = ''";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $data = $sqlserver->query($sqlquery);

        //$data = array(array('chanpinxingzhi'=>$startDate,'shuliang'=>$endDate,'xiaoshoujinger'=>$newPMList,'xiaoshoumaoli'=>$ptlist,'maolilv'=>$newSSList));

        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '产品性质');
        $currentSheet->setCellValue('B'.$m, '数量');
        $currentSheet->setCellValue('C'.$m, '销售金额');
        $currentSheet->setCellValue('D'.$m, '销售毛利');
        $currentSheet->setCellValue('E'.$m, '毛利率%');
        $currentSheet->setCellValue('F'.$m, '销售额占比');

        $cellIndex = 1;
        foreach ($data as $key => $result){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$result['u_is3C'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$result['sumqty'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$result['sumlinetotal'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$result['sumprofit'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$result['profitrate'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$result['percentage'], \PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    public function pinpaianalysis20171018()
    {
        $this->display();
    }

    public function pinpaianalysis20171018data()
    {
        // from html
        $startDate = trim(I('startDate'));
        $endDate = trim(I('endDate'));
        $ppList = I('ppList');
        //$ppName=mb_convert_encoding($ppName,'gbk','utf-8');
        $ptList = I('ptlist');
        $queryType = I('queryType');

        import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $ppName = $sapBrandImpl->getPPNameByPPList($ppList);

        $sqlquery = "EXEC [dbo].[A_BrandAnalysis] 
@pmanagerstring = '',
@cardcodeString = '".$ptList."',
@FromDate = '".$startDate."' ,
@ToDate = '".$endDate."',
@brandstring = '".$ppName."',
@department = '',
@isgrouped = '".$queryType."'";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $result = $sqlserver->query($sqlquery);

        //$result = array(array('itemCode'=>$startDate,'itemName'=>$endDate,'sumQty'=>$ppName,'sumProfit'=>$ptList,'profitRate'=>$queryType,'ppID'=>$ppList));
        echo json_encode(array('total'=>1,'data'=>$result));exit;

    }

    public function pinpaianalysis20171018excel()
    {
        // from html
        $startDate = trim(I('startDate'));
        $endDate = trim(I('endDate'));
        $ppList = I('ppList');
        //$ppName=mb_convert_encoding($ppName,'gbk','utf-8');
        $ptList = I('ptlist');
        $queryType = I('queryType');

        import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $ppName = $sapBrandImpl->getPPNameByPPList($ppList);

        $sqlquery = "EXEC [dbo].[A_BrandAnalysis] 
@pmanagerstring = '',
@cardcodeString = '".$ptList."',
@FromDate = '".$startDate."' ,
@ToDate = '".$endDate."',
@brandstring = '".$ppName."',
@department = '',
@isgrouped = '".$queryType."'";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $data = $sqlserver->query($sqlquery);

        //$data = array(array('itemCode'=>$startDate,'itemName'=>$endDate,'sumQty'=>$ppName,'sumProfit'=>$ptList,'profitRate'=>$queryType,'ppID'=>$ppList));

        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '品牌');
        $currentSheet->setCellValue('B'.$m, '平台');
        $currentSheet->setCellValue('C'.$m, '数量');
        $currentSheet->setCellValue('D'.$m, '销售额');
        $currentSheet->setCellValue('E'.$m, '毛利');
        $currentSheet->setCellValue('F'.$m, '毛利率%');

        $cellIndex = 1;
        foreach ($data as $key => $result){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$result['brand'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$result['shortname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$result['sumqty'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$result['sumlinetotal'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$result['sumprofit'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('F'.$cellIndex,$result['profitrate'], \PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    public function chanpinmanageranalysis20171018()
    {
        $this->display();
    }

    public function chanpinmanageranalysis20171018data()
    {
        // from html
        $startDate = trim(I('startDate'));
        $endDate = trim(I('endDate'));
        $ppList = I('ppList');
        //$ppName=mb_convert_encoding($ppName,'gbk','utf-8');

        $pmlist = I('pmlist');
        $newPMList = $this->pmMemberMerge($pmlist);

        $queryType = I('queryType');

        import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $ppName = $sapBrandImpl->getPPNameByPPList($ppList);

        $sqlquery = "EXEC [dbo].[A_PmanagerBrandAnalysis] 
@pmanagerstring ='".$newPMList."',
@cardcodeString = '',
@FromDate = '".$startDate."',
@ToDate = '".$endDate."',
@brandstring = '".$ppName."',
@department = '',
@isgrouped = '".$queryType."'";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $result = $sqlserver->query($sqlquery);

        //$result = array(array('itemCode'=>$startDate,'itemName'=>$endDate,'sumQty'=>$ppName,'sumProfit'=>$newPMList,'profitRate'=>$queryType));
        echo json_encode(array('total'=>1,'data'=>$result));exit;

    }

    public function chanpinmanageranalysis20171018excel()
    {
        // from html
        $startDate = trim(I('startDate'));
        $endDate = trim(I('endDate'));
        $ppList = I('ppList');
        //$ppName=mb_convert_encoding($ppName,'gbk','utf-8');

        $pmlist = I('pmlist');
        $newPMList = $this->pmMemberMerge($pmlist);

        $queryType = I('queryType');

        import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $ppName = $sapBrandImpl->getPPNameByPPList($ppList);

        $sqlquery = "EXEC [dbo].[A_PmanagerBrandAnalysis] 
@pmanagerstring ='".$newPMList."',
@cardcodeString = '',
@FromDate = '".$startDate."',
@ToDate = '".$endDate."',
@brandstring = '".$ppName."',
@department = '',
@isgrouped = '".$queryType."'";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $data = $sqlserver->query($sqlquery);

        //$data = array(array('itemCode'=>$startDate,'itemName'=>$endDate,'sumQty'=>$ppName,'sumProfit'=>$newPMList,'profitRate'=>$queryType));

        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '产品负责人');
        $currentSheet->setCellValue('B'.$m, '品牌');
        $currentSheet->setCellValue('C'.$m, '数量');
        $currentSheet->setCellValue('D'.$m, '销售额');
        $currentSheet->setCellValue('E'.$m, '毛利');
        $currentSheet->setCellValue('F'.$m, '毛利率%');

        $cellIndex = 1;
        foreach ($data as $key => $result){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$result['pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$result['U_brand'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$result['sumqty'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$result['sumlinetotal'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$result['sumprofit'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('F'.$cellIndex,$result['profitrate'], \PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    public function xiaoshoutrendanalysis20171018()
    {
        $this->display();
    }

    public function xiaoshoutrendanalysis20171018data()
    {
        // from html
        $startDate = trim(I('startDate'));
        $endDate = trim(I('endDate'));

        $ppList = I('ppList');
        //$ppName=mb_convert_encoding($ppName,'gbk','utf-8');

        import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $ppName = $sapBrandImpl->getPPNameByPPList($ppList);

        $itemName = I('itemName');
        $itemName=mb_convert_encoding($itemName,'gbk','utf-8');

        $pmlist = I('pmlist');
        $newPMList = $this->pmMemberMerge($pmlist);

        $ptlist = I('ptlist');

        $sslist = I('sslist');

        if(!empty($sslist)) {
            $newSSList = $this->ssMemberMerge($sslist);
        }else{
            $newSSList = '';
        }

        $sqlquery = "EXEC [dbo].[A_SalesTrends] 
@pmanagerstring = '".$newPMList."',
@cardcodeString = '".$ptlist."',
@FromDate = '".$startDate."',
@ToDate = '".$endDate."',
@brandstring = '".$ppName."',
@namestring = '".$itemName."',
@cmanagerstring = '".$newSSList."',
@department = ''";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $result = $sqlserver->query($sqlquery);

        //$result = array(array('chanpinxingzhi'=>$ppName,'shuliang'=>$itemName,'xiaoshoujinger'=>$newPMList,'xiaoshoumaoli'=>$ptlist,'maolilv'=>$newSSList,'ppID'=>$ppList));
        echo json_encode(array('total'=>1,'data'=>$result));exit;

    }

    public function xiaoshoutrendanalysis20171018excel()
    {
        // from html
        $startDate = trim(I('startDate'));
        $endDate = trim(I('endDate'));

        $ppList = I('ppList');
        //$ppName=mb_convert_encoding($ppName,'gbk','utf-8');

        import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $ppName = $sapBrandImpl->getPPNameByPPList($ppList);

        $itemName = I('itemName');
        $itemName=mb_convert_encoding($itemName,'gbk','utf-8');

        $pmlist = I('pmlist');
        $newPMList = $this->pmMemberMerge($pmlist);

        $ptlist = I('ptlist');

        $sslist = I('sslist');

        if(!empty($sslist)) {
            $newSSList = $this->ssMemberMerge($sslist);
        }else{
            $newSSList = '';
        }

        $sqlquery = "EXEC [dbo].[A_SalesTrends] 
@pmanagerstring = '".$newPMList."',
@cardcodeString = '".$ptlist."',
@FromDate = '".$startDate."',
@ToDate = '".$endDate."',
@brandstring = '".$ppName."',
@namestring = '".$itemName."',
@cmanagerstring = '".$newSSList."',
@department = ''";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $data = $sqlserver->query($sqlquery);

        //$data = array(array('chanpinxingzhi'=>$ppName,'shuliang'=>$itemName,'xiaoshoujinger'=>$newPMList,'xiaoshoumaoli'=>$ptlist,'maolilv'=>$newSSList,'ppID'=>$ppList));
        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '月份');
        $currentSheet->setCellValue('B'.$m, '数量');
        $currentSheet->setCellValue('C'.$m, '销售金额');
        $currentSheet->setCellValue('D'.$m, '销售毛利');
        $currentSheet->setCellValue('E'.$m, '毛利率%');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['YearMonth'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$value['sumqty'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$value['sumlinetotal'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$value['sumprofit'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$value['profitrate'], \PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    public function xiaoshousummary20171018()
    {

        $this->display();
    }

    public function xiaoshousummary20171018data()
    {
        // from html
        $startDate = trim(I('startDate'));
        $endDate = trim(I('endDate'));

        $ppList = I('ppList');
        import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $ppName = $sapBrandImpl->getPPNameByPPList($ppList);

        $itemName = I('itemName');
        $itemName=mb_convert_encoding($itemName,'gbk','utf-8');

        $pmlist = I('pmlist');
        $newPMList = $this->pmMemberMerge($pmlist);

        $ptlist = I('ptlist');

        $sslist = I('sslist');
        if(!empty($sslist)) {
            $newSSList = $this->ssMemberMerge($sslist);
        }else{
            $newSSList = '';
        }

        $sqlquery = "EXEC [dbo].[A_SalesSummary] 
@pmanagerstring = '".$newPMList."',
@cardcodeString = '".$ptlist."',
@FromDate = '".$startDate."',
@ToDate = '".$endDate."',
@brandstring = '".$ppName."',
@namestring = '".$itemName."',
@cmanagerstring = '".$newSSList."',
@department = ''";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $result = $sqlserver->query($sqlquery);

        //$result = array(array('chanpinxingzhi'=>$ppName,'shuliang'=>$itemName,'xiaoshoujinger'=>$newPMList,'xiaoshoumaoli'=>$ptlist,'maolilv'=>$newSSList));
        //var_dump($result);die();
        echo json_encode(array('total'=>1,'data'=>$result));exit;

    }

    public function xiaoshousummary20171018excel()
    {

        // from html
        $startDate = trim(I('startDate'));
        $endDate = trim(I('endDate'));

        $ppList = I('ppList');
        import("@.ORG.Util.SapBrand");
        $sapBrandImpl = SapBrand::getInstance();
        $ppName = $sapBrandImpl->getPPNameByPPList($ppList);

        $itemName = I('itemName');
        $itemName=mb_convert_encoding($itemName,'gbk','utf-8');

        $pmlist = I('pmlist');
        $newPMList = $this->pmMemberMerge($pmlist);

        $ptlist = I('ptlist');

        $sslist = I('sslist');
        if(!empty($sslist)) {
            $newSSList = $this->ssMemberMerge($sslist);
        }else{
            $newSSList = '';
        }

        $sqlquery = "EXEC [dbo].[A_SalesSummary] 
@pmanagerstring = '".$newPMList."',
@cardcodeString = '".$ptlist."',
@FromDate = '".$startDate."',
@ToDate = '".$endDate."',
@brandstring = '".$ppName."',
@namestring = '".$itemName."',
@cmanagerstring = '".$newSSList."',
@department = ''";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        //echo $sqlquery;die();
        $data = $sqlserver->query($sqlquery);

        //$data = array(array('chanpinxingzhi'=>$ppName,'shuliang'=>$itemName,'xiaoshoujinger'=>$newPMList,'xiaoshoumaoli'=>$ptlist,'maolilv'=>$newSSList,'ppID'=>$ppList));
        //Export to Excel:
        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '品牌');
        $currentSheet->setCellValue('B'.$m, '数量');
        $currentSheet->setCellValue('C'.$m, '销售金额');
        $currentSheet->setCellValue('D'.$m, '销售毛利');
        $currentSheet->setCellValue('E'.$m, '毛利率%');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['brand'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$value['sumqty'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$value['sumlinetotal'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$value['sumprofit'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$value['profitrate'], \PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }

    public function wuliaojikucunPOP20171018()
    {
        $itemCode = I('itemCode');
        $this->assign("itemCode",$itemCode);
        $this->display();
    }

    public function wuliaojikucunPOP20171018data()
    {
        $itemCode = $_REQUEST['itemCode'];

        $pageIndex = I('pageIndex');
        $pageSize = I('pageSize')?:50000;
        $where='1 = 1 ';

        // 员工ID
        $auth_id = session('auth_id');
        $emp_no = session('emp_no');
        // 员工部门 <= OA
        import("@.ORG.Util.UserRole");
        $userRole = new UserRole();
        $userInfo = $userRole->getUserRole($auth_id,$emp_no);
        $pmList = $userInfo['pmlist'];
        $userDept = $userInfo['dept'];

        $sqlquery = "EXEC [dbo].[A_Itemreport]
		@itemname = '".$itemCode."',
		@pmanagerstring = '',
		@cardcodeString= '',
		@department = 'eccbc87e4b5ce2fe28308fd9f2a7baf3',
		@isgrouped= '1',
		@codeOrname='code'";

        //import("@.ORG.Util.Sqlserver");
        //$sqlserver  = new Sqlserver();
        import("@.ORG.Util.Sqlserverforreport");
        $sqlserver  = new Sqlserverforreport();
        $result = $sqlserver->query($sqlquery);
        //echo $sqlquery;die();
        //var_dump(count($result));die();

        //$result = array(array('itemCode'=>$itemName,'itemName'=>$ptList,'sumQty'=>$queryType));
        echo json_encode(array('total'=>1,'data'=>$result));exit;

    }

}